Как разбить строку на основе «:» в MS-Excel?

Мой столбец Excel заполнен такими словами:

  1.) ABC: DCF2.) DCF: FED  

Я хочу разбить каждое слово на основе «:» и поместить результат в соседние столбцы так, чтобы «ABC: DCF» в ячейке «A: 1» превратилось в «ABC» в ячейке «B: 1» и «DCF» в ячейке «C: 1», а также соответствующие значения в каждом столбце. Как это сделать?


Перейдите на вкладку «Данные», затем выберите «Текст в столбцы». Позже выберите вариант «С разделителями», затем выберите «Другое» и поместите любой желаемый разделитель.


86

Текст в столбцы будет работать. Другой вариант, если вы хотите сохранить исходное значение, — использовать формулы:
в B1

  = left (a1, find (":", a1  ) -1)  

в C1

  = mid (a1, find (":", a1) + 1, len  (a1))  

поделиться | улучшить этот ответ | |
ответил 03 октября 2012 г. в 16:34
  • 2
    Исходное значение может быть сохранено даже с другим решением (вы можете указать другой столбец для хранения новых значений), но Мне больше нравится это решение, потому что оно позволяет всегда иметь актуальные значения (то есть, если вы измените A1, B1 и C1 будут обновляться, а параметр text-to-column — нет). — Psychowood 16 июля ’13 в 14:43
  • Это блестящее решение — jsg 28 июня ’17 в 10:09
добавить комментарий |

Текст в столбцы будет работать. Другой вариант, если вы хотите сохранить исходное значение, — использовать формулы:
в B1

  = left (a1, find (":", a1  ) -1)  

в C1

  = mid (a1, find (":", a1) + 1, len  (a1))  

26

Если вы можете использовать VBA, вы можете использовать функцию Split () . Вот функция, определяемая пользователем (UDF), которую вы можете использовать в ячейке. Он разбивается на выбранный вами символ и возвращает n -й элемент разделенного списка.

См. Как мне добавить VBA в MS Office? Для получения информации о том, как определить UDF.

  Функция STR_SPLIT (str, sep, n) As String Dim V () As String V = Split (str, sep) STR_SPLIT = V (n - 1) End Function  

Итак, вам нужно ввести:

  = STR_SPLIT (A1, ":", 1)//для первого  half = STR_SPLIT (A1, ":", 2)//для второй половины  

поделиться | улучшить этот ответ | |
отредактировано 20 марта ’17 в 10:17
Сообщество ♦
1
ответили 03 окт., 18:42
  • 1
    Очень мило, не знал, что создавать свои собственные формулы так просто — cowls 13 мая 2014 г., 11:13
  • 1
    Это идеально подходит для разделения URL-адреса на его составные части. — Underverse 2 июн 2015, в 1:42
добавить комментарий |

Если вы можете использовать VBA, вы можете использовать функцию Split () . Вот функция, определяемая пользователем (UDF), которую вы можете использовать в ячейке. Он разбивается на выбранный вами символ и возвращает n -й элемент разделенного списка.

См. Как мне добавить VBA в MS Office? Для получения информации о том, как для определения UDF.

  Функция STR_SPLIT (str, sep, n) As String Dim V () As String V = Split (str, sep) STR_SPLIT = V (n -  1) Конечная функция  

Итак, вам необходимо ввести:

  = STR_SPLIT (A1, ":", 1) //для первой половины = STR_SPLIT (A1, ":", 2)//для второй половины  

8

Вставьте его в B1 и заполните его столбцами справа и строками вниз:

= ОБРЕЗАТЬ (MID (ПОДСТАВИТЬ ($ A1, ":", ПОВТОР ("", 999)), COLUMNS ($ A: A) * 999-998,999))

Изменить: я ранее публиковал локализованную версию формулы, где ‘,’ было заменено на ‘;’. Это не работает в версии Excel для США:

= TRIM (MID (SUBSTITUTE ($ A1; ":"; REPT (""; 999 )); КОЛОННЫ ($ A: A) * 999-998; 999))

поделиться | улучшить этот ответ | |
отредактировано 25 сен 2016 в 18:35
ответил 5 сен 2016, 16:44
  • 1
    Добро пожаловать в суперпользователя. Не могли бы вы добавить к своему ответу несколько предложений, чтобы объяснить, что он делает и как работает? Это повысит его образовательную ценность. Благодарю. — fixer1234 5 сен 2016, в 18:02
  • Да, конечно. Он делает то же самое, что и вкладка «Текст в столбцы из данных», за исключением того, что это не формула. Вы можете заменить «:» другим разделителем или ссылаться на разделитель из другой ячейки. — Харди Уутма, 7 сен 2016, в 8:11
  • Excel сообщает, что это недействительная формула, когда вы вставляете ее в ячейку. Пожалуйста, проверьте и обновите. — thilina R 23 сен 2016, в 14:01
  • Привет, thilina R! Спасибо, что сообщили. Сделал корректировку для американской версии Excel. Пожалуйста, дайте мне знать, если у вас возникнут проблемы с этим сейчас или что-то неясно. — Харди Уутма, 25 сен 2016, в 06:39
  • Очень красиво. На данный момент это единственный ответ, который позволяет вам иметь дело с любым количеством разделителей, без создания собственной функции. — CWilson, 15 дек. ’16 в 18:01
| показать 1 дополнительный комментарий

Вставьте его в B1 и заполните им столбцы справа и строки вниз:

= ОБРЕЗАТЬ (MID (ПОДСТАВИТЬ ($ A1, ":", ПОВТОР ("", 999)), COLUMNS ($ A: A) * 999-998,999))

Изменить: я ранее опубликовал локализованную версию формулы, где ‘,’ было заменено на ‘;’. Это не работает в версии Excel для США:

= TRIM (MID (SUBSTITUTE ($ A1; ":"; REPT (""; 999 )); КОЛОННЫ ($ A: A) * 999-998; 999))


0

Для этого вы также можете использовать формулу массива.

Если у вас есть ABC: DEF: GHI в ячейке A1, тогда, если вы введете следующая формула в B1: D1 разделит текст в A1 символом двоеточия.

= ЕСЛИОШИБКА (MID («:» & $ A1 & «:», SMALL (IF (MID ( «:» & $ A1 & «:», ROW (1 $: 999 $), 1) = «:», ROW (1 $: 999 $), «»), COLUMN () — 1) + 1, SMALL (IF (MID ( «:» & $ A1 & «:», СТРОКА (1 $: 999 $), 1) = «:», СТРОКА (1 $: 999 $), «»), (COLUMN ())) — МАЛЕНЬКИЙ (IF (MID («: «& $ A1 &»: «, СТРОКА (1 $: 999 $), 1) =»: «, СТРОКА (1 $: 999 $),» «), COLUMN () — 1) -1),» «)

Вам нужно нажать CTRL + ENTER, чтобы ввести формулу. Основная часть, которая повторяется 3 раза, — это этот бит … IF (MID («:» & $ A1 & «:», ROW ($ 1: $ 999 ), 1) = «:», ROW (1 $: 999 $), «»), который получает массив позиций двоеточия (используя строки с 1 по 999, хотя вы можно было бы использовать больше). Это возвращает массив

1; «»; «»; «»; 5; «»; «»; «»; 9; «»; «»; «»; 13; «» ; «»; «»

Используя функцию МАЛЕНЬКИЙ, вы получаете 1-е и второе наименьшие значения, которые дают вам 1 и 5. Затем она использует функцию MID для получения строки между первым и 5-м символами. (имея в виду строку «: ABC: DEF: FHI:», потому что мы добавили разделитель в начало и конец). Затем в следующем столбце вы получите текст между 5-м и 9-м символами и т. Д.

share | улучшить этот ответ | |
ответил 03 июня ’20 в 14:36 ​​
добавить комментарий |

Для этого вы также можете использовать формулу массива.

Если у вас есть ABC: DEF: GHI в ячейке A1, тогда, если вы введете следующую формулу в B1: D1, она разделит текст в A1 символом двоеточия.

= ЕСЛИОШИБКА (MID («:» & $ A1 & «:» , МАЛЕНЬКИЙ (ЕСЛИ (СРЕДНЕЕ («:» & $ A1 & «:», СТРОКА (1 $: 999 долл. США), 1) = «:», СТРОКА (1 долл. США: 999 долл. США), «»), COLUMN () — 1) +1 , МАЛЕНЬКИЙ (ЕСЛИ (СРЕДНЕЕ («:» & $ A1 & «:», СТРОКА (1 долл. США: 999 долл. США), 1) = «:», СТРОКА (1 долл. США: 999 долл. США), «»), (КОЛОНКА ())) — МАЛЕНЬКАЯ (IF (MID («:» & $ A1 & «:», ROW ($ 1: $ 999), 1) = «:», ROW ($ 1: $ 999), «»), COLUMN () — 1) -1), «»)

Чтобы ввести формулу, вам нужно нажать CTRL + ENTER. Основная часть, которая повторяется 3 раза, — это бит … IF (MID («:» & $ A1 & «: «, ROW ($ 1: $ 999), 1) =»: «, ROW ($ 1: $ 999),» «), который получает массив позиций двоеточия (используя строки с 1 по 999, хотя вы могли бы использовать больше). Это возвращает массив

1; «»; «»; «»; 5; «»; «»; «»; 9; «»; «»; «»; 13; «» ; «»; «»

Используя функцию МАЛЕНЬКИЙ, вы получаете 1-е и второе наименьшие значения, которые дают вам 1 и 5. Затем она использует функцию MID для получения строки между первым и 5-м символами. (имея в виду строку «: ABC: DEF: FHI:», потому что мы добавили разделитель в начало и конец). Затем в следующем столбце вы получите текст между 5-м и 9-м символами и т. Д.

Оцените статью
somehone.ru
Добавить комментарий