Как разделить текст в Excel 5 способами

В этом руководстве мы собираемся показать вам, как разделить текст в Excel с помощью разделителя.

Скачать готовый шаблон

У нас есть образец данных, который содержит объединенные значения, разделенные символами “|”. Важно, чтобы данные включали определенный символ-разделитель между каждым фрагментом данных, чтобы упростить разделение текста.

Функция преобразования текста в столбцы для разделения текста

При разделении текста в Excel разбиение текста на столбцы является одним из наиболее распространенных методов. Функцию разбиения текста на столбцы можно использовать во всех версиях Excel. Эта функция может разделять текст по определенному количеству символов или разделителю.

1. Начните с выбора ваших данных. Вы можете использовать более одной ячейки в столбце.
2. Нажмите Данные> Текст в столбцы на ленте.
3. На первом шаге мастера у вас есть 2 варианта на выбор — это методы нарезки. Поскольку наши данные в этом примере разделены разделителями, наш выбор будет разделен разделителями.
4. Нажмите Далее , чтобы продолжить
5. Выберите разделители, подходящие для ваших данных, или выберите длину символа и нажмите Далее.
6. Выберите соответствующие типы данных для столбцов и целевой ячейки. Пожалуйста, обратите внимание, что если целевая ячейка совпадает с ячейкой, в которой находятся ваши данные, исходные данные будут перезаписаны.
7. Нажмите Готово , чтобы увидеть результат.

Использование формул для разделения текста

В Excel есть множество текстовых формул, которые можно использовать для определения разделителей и анализа данных. При использовании формул для этого Excel автоматически обновляет проанализированные значения при обновлении источника.

Формула, используемая в этом примере, использует функцию динамического массива Microsoft 365, которая позволяет вам заполнить несколько ячеек без использования формул массива. Если вы видите формулу ПОСЛЕДОВАТЕЛЬНОСТИ, вы можете использовать этот метод.

Синтаксис

= ОБРЕЗАТЬ (СЕРЕДИНА(ЗАМЕНИТЬ( текст, разделитель, REPT( “ “, LEN( текст))),(ПОСЛЕДОВАТЕЛЬНОСТЬ (1, количество столбцов) — 1) * LEN (текст) + 1,LEN(текст)))

Как это работает

Формула сначала заменяет каждый символ-разделитель пробелами. (см. SUBSTITUTE) Количество пробелов будет равно количеству символов в исходной строке, что является достаточным количеством пробелов для разделения каждого блока строки (см. REPT и LEN).

Функция SEQUENCE генерирует массив чисел, начинающихся с 1, вплоть до максимального количества столбцов. Умножение этих последовательных чисел на длину исходной строки возвращает номера символов, указывающие начало каждого блока.

Этот подход использует функцию MID для разбора каждого блока строк с заданным начальным номером символа и количеством возвращаемых символов. Поскольку разделители заменены пробелами, каждый анализируемый блок включает эти пробелы вокруг фактической строки. Затем функция TRIM удаляет эти пробелы.

Flash Fill

Flash Fill — это инструмент Excel, который может определять шаблон при вводе данных. Распространенным примером является разделение или слияние имени и фамилии. Допустим, столбец A содержит комбинации имени и фамилии. Если вы введете соответствующее имя в той же строке для столбца B, Excel покажет вам предварительный просмотр для остальной части столбца.

Пожалуйста, обратите внимание, что флэш-заливка доступна только для Excel 2013 и более новых версий.

Вы можете увидеть то же поведение со строками, использующими разделители для разделения данных. Просто выберите ячейку в соседнем столбце и начните вводить текст. Иногда Excel отображает предварительный просмотр. Если нет, нажмите Ctrl + E, как показано ниже, чтобы разделить текст.

Мощный запрос

Power Query — это мощная функция не только для разделения текста, но и для управления данными в целом. Power Query поставляется с собственным инструментом разделения текста, который позволяет вам разделять текст несколькими способами, например, по разделителям, количеству символов или регистру букв.

Если вы используете Excel 2016 или новее, включая Microsoft 365, вы можете найти параметры Power Query в разделе «Получение и преобразование» вкладки «Данные «. Пользователям Excel 2010 и 2013 следует загрузить и установить Power Query в качестве надстройки.

1. Выделите ячейки, содержащие текст.
2. Выберите Данные > из листа. Если данных нет в таблице Excel, Excel сначала преобразует их в таблицу Excel.
3. Как только откроется окно Power Query, найдите разделенный столбец на вкладке Преобразование и щелкните, чтобы просмотреть параметры.
4. Выберите подход, который соответствует вашему расположению данных. В данных в нашем примере используется разделитель, поскольку данные разделяются символом “|”.
5. Power Query покажет символ-разделитель. Если вы не видите ожидаемый разделитель, выберите из списка или введите его самостоятельно.
6. Нажмите OK , чтобы разделить текст.
7. Если ваши данные содержат заголовки в первой строке, как в нашем примере, нажмите Преобразовать > Использовать первую строку в качестве заголовков, чтобы сохранить их в качестве заголовков.
8. Если вы удовлетворены результатом, нажмите кнопку Главная> Закрыть и загрузить, чтобы переместить разделенные данные в вашу книгу.

VBA

VBA — это последний вариант разделения текста, который мы хотим показать в этой статье. Вы можете использовать VBA двумя способами разделения текста:

1. Вызвав функцию преобразования текста в столбцы с помощью VBA,
2. Использование функции Split в VBA для создания массива вложенных строк и использование цикла для распределения вложенных строк по соседним ячейкам в одной строке.

Текст в столбцы с помощью VBA

Следующий код позволяет разделить данные из выбранных ячеек на соседние столбцы. Обратите внимание, что каждый поддерживаемый разделитель указан в качестве аргумента, который вы можете включить или отключить, присвоив ему значение True или False. Вкратце, True означает, что вы хотите установить этот аргумент в качестве разделителя, а False означает игнорировать этот символ.

Простой способ сгенерировать код VBA для разделения текста — записать макрос. Запустите раздел записи с помощью кнопки «Записать макрос» на вкладке «Разработчик » и используйте функцию «Разбиение текста на столбцы»…………. Как только запись будет остановлена, Excel сохранит код того, что вы сделали во время записи.

Код:

Вложите VBATextToColumns_Multiple()
Затемните MyRange как диапазон
Установите MyRange = Selection ‘Для работы со статическим диапазоном, замените выделение с помощью объекта Range, например, Range(“B6:B12”)

MyRange.TextToColumns _
Назначение:=MyRange(1, 1).Смещение(, 1), _
Тип данных:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
Последовательный предел:=False, _
Табуляция:=True, _
Точка с запятой:=False, _
Запятая:=False, _
Пробел:=False, _
Другое:=True, _
OtherChar:=»|»
Конец Субмарина

Функция разделения

Функция разделения может быть полезна, если вы хотите сохранить разделенные блоки в массиве. Вы можете использовать этот метод только для разделения из-за ограничения с одним разделителем. Следующий код перебирает каждую ячейку в выбранном столбце, разделяет и сохраняет текст с помощью разделителя “|” и использует другой цикл для заполнения значений массива в ячейках. Конечный столбец целиком.Команда автозаполнения регулирует ширину столбца.

Код:

Sub SplitText()
Затемнить stringArray() как строку, ячейку как диапазон, i как целое число
для каждой ячейки в выделении ‘Для работы со статическим диапазоном замените выделение с помощью объекта Range, например, Range(“B6:B12”)
stringArray = Split(Cell, «|») ‘Измените разделитель символом, соответствующим вашим данным
, для i = 0 на UBound(stringArray)
ячейка.Смещение (, i + 1) = stringArray(i)
ячейка.Смещение (, i + 1).Целая колонка.Автозаполнение ‘Это для ширины столбца и необязательно.
Далее я
заканчиваю подраздел Next