Как расцепить текст в Excel

Руководство демонстрирует, как можно использовать совершенно новую функцию TEXTSPLIT в Excel 365 для разбиения текстовых строк с помощью указанных вами разделителей.

В разных ситуациях может возникнуть необходимость разделить содержимое ячеек в Excel. В более ранних версиях программы для этого уже существовали несколько инструментов, таких как "Текст по столбцам" и функция Fill Flash. Теперь в нашем распоряжении есть специальная функция TEXTSPLIT, которая позволяет разбивать строку на несколько ячеек по столбцам и/или строкам, в зависимости от заданных параметров.

Функция TEXTSPLIT в Excel

Функция TEXTSPLIT в Excel служит для разделения текстовых строк с использованием заданного разделителя по столбцам или строкам. Результатом работы является динамический массив, который автоматически размещается в нескольких ячейках.

Функция включает в себя до шести аргументов, из которых обязательными являются только первые два.

TEXTSPLIT(текст; разделитель_столбцов; [разделитель_строк]; [игнорировать_пустые]; [режим_сопоставления]; [дополнить])

1. Текст (обязательно) — строка текста, которую нужно разделить. Может быть указана в виде строки или ссылки на ячейку.
2. Разделитель столбцов (обязательно) — символы, по которым нужно разделить текст по столбцам. Если этот аргумент пропущен, обязательно задается разделитель строк.
3. Разделитель строк (необязательно) — символы, по которым текст будет разделяться по строкам.
4. Игнорировать пустые (необязательно) — указывает, следует ли игнорировать пустые значения:
   • FALSE (по умолчанию) — создаются пустые ячейки для последовательных разделителей без значений между ними.
   • TRUE — игнорируются пустые значения, то есть не создаются пустые ячейки при наличии двух или более подряд идущих разделителей.
5. Режим сопоставления (необязательно) — определяет, чувствительна ли функция к регистру символов:
   • 0 (по умолчанию) — учитывается регистр.
   • 1 — регистр не учитывается.
6. Дополнить (необязательно) — значение, которое будет использоваться вместо отсутствующих данных в двумерных массивах. По умолчанию возвращается ошибка #N/A.

Например, для того чтобы разделить текстовую строку в ячейке A2 на несколько ячеек, используя запятую и пробел в качестве разделителя, формула будет выглядеть так:

=TEXTSPLIT(A2; ", ")

Доступность функции TEXTSPLIT

Функция TEXTSPLIT доступна только в Excel для Microsoft 365 (как для Windows, так и для Mac), а также в веб-версии Excel.

Советы:

1. В версиях Excel, где функция TEXTSPLIT отсутствует, можно использовать мастер "Текст по столбцам" для разделения ячеек.
2. Чтобы выполнить обратную задачу — объединить содержимое нескольких ячеек в одну, используя определенный разделитель, применяйте функцию TEXTJOIN.

Простой пример использования TEXTSPLIT для разбиения ячеек в Excel

Горизонтальное разделение ячейки по столбцам

Чтобы разделить содержимое ячейки на несколько столбцов, необходимо в качестве первого аргумента указать ссылку на ячейку с исходной строкой, а во втором аргументе — разделитель, по которому будет происходить разделение.

Например, чтобы разделить строку в ячейке A2 по запятой, используем следующую формулу:

=TEXTSPLIT(A2;, ",")

Разделитель в данном случае — это запятая, заключенная в двойные кавычки (",").

Результатом будет размещение каждого элемента, отделенного запятой, в отдельном столбце.

Вертикальное разделение ячейки по строкам

Для разделения текста по строкам используется третий аргумент — разделитель строк. При этом второй аргумент разделитель столбцов можно не указывать.

Пример: чтобы разделить содержимое ячейки A2 на несколько строк, используйте такую формулу:

=TEXTSPLIT(A; ;",")

Заметьте, что в обоих примерах формула вводится только в одну ячейку (например, C2), а соседние ячейки автоматически заполняются значениями. Результирующий массив (или область пролива) будет выделен синей рамкой, что указывает на то, что все значения внутри него вычислены одной формулой.

Разделение текста по подстроке

Во многих случаях значения в исходной строке разделяются последовательностью символов, например, запятой и пробелом. Чтобы обработать такую ситуацию, используйте подстроку в качестве разделителя.

Пример: для того чтобы разделить текст в ячейке A2 на несколько столбцов по запятой и пробелу, примените следующий разделитель: ", ".

Формула будет выглядеть так:

=TEXTSPLIT(A2; ", ")

Эта формула вводится в ячейку B2, а затем копируется на другие ячейки, если необходимо.

Одновременное разделение текста на столбцы и строки

Чтобы разбить текстовую строку одновременно на строки и столбцы, необходимо задать оба разделителя в формуле TEXTSPLIT.

Пример: для разделения текста в ячейке A2 на строки и столбцы, задаем следующие значения:

1. равно ("=") — для разделителя столбцов.
2. запятая и пробел (", ") — для разделителя строк.

Формула примет следующий вид:

=TEXTSPLIT(A2; "="; ", ")

Результатом будет двумерный массив, состоящий из двух столбцов и трех строк.

Разделение по нескольким разделителям

Если исходная строка содержит несколько разных разделителей или разделители непоследовательны, можно использовать константу массива для аргумента разделителя, например, {"x","y","z"}.

На изображении ниже показано, как текст в ячейке A2 разделен как запятыми (",") так и точками с запятой (";"), с пробелами и без. Чтобы разделить строку вертикально по всем четырем видам разделителей, используйте следующую формулу:

=TEXTSPLIT(A2; ; {",", ", ", ";", "; "})

Также можно ограничиться только запятой (",") и точкой с запятой (";"), а лишние пробелы убрать с помощью функции TRIM:

=TRIM(TEXTSPLIT(A2; ; {",", ";"}))

Разделение строки по нескольким разделителям.

Разбивка текста с игнорированием пустых значений

Если в строке есть два или более подряд идущих разделителя без значений между ними, вы можете выбрать, хотите ли вы игнорировать такие пустые значения. Этот аспект поведения регулируется четвертым параметром ignore_empty, который по умолчанию равен FALSE.

По умолчанию функция TEXTSPLIT не игнорирует пустые значения. Это стандартное поведение полезно для работы со структурированными данными, как в приведенном ниже примере.

В этой таблице в некоторых строках отсутствуют оценки. Формула TEXTSPLIT с параметром ignore_empty, не указанным или установленным в значение FALSE, прекрасно справляется с этой задачей, создавая пустую ячейку для каждого отсутствующего значения.

Формула:

=TEXTSPLIT(A2; ", ")

Или же:

=TEXTSPLIT(A2; ", "; FALSE)

В результате все значения появляются в нужных столбцах. Разделенные строки создают пустые ячейки для отсутствующих данных.

Если же ваши строки содержат однородные данные, может быть разумно игнорировать пустые значения. Для этого установите параметр ignore_empty в значение TRUE или 1.

Например, чтобы разделить строки ниже, помещая каждое умение в отдельную ячейку без пропусков, используется следующая формула:

=TEXTSPLIT(A2; ", "; ;TRUE)

В данном случае отсутствующие значения между подряд идущими разделителями будут полностью проигнорированы.

Разделение текста с учетом или без учета регистра

Для управления учетом регистра при разделении по разделителю используется пятый параметр функции TEXTSPLIT, называемый match_mode.

По умолчанию параметр match_mode установлен в 0, что делает функцию TEXTSPLIT чувствительной к регистру.

В этом примере числа разделены строчными "x" и заглавными "X".

Формула с учетом регистра примет в качестве разделителя только строчную букву "x":

=TEXTSPLIT(A2; " x ")

Обратите внимание, что в разделителе на обеих сторонах буквы "x" стоят пробелы, чтобы избежать появления лишних пробелов в начале и конце результата.

Чтобы отключить учет регистра, установите значение 1 для параметра match_mode, что заставит функцию игнорировать регистр:

=TEXTSPLIT(A2; " x ", , ,1)

Теперь строки будут корректно разделены любым из разделителей, независимо от регистра.

Дополнение недостающих значений в 2D-массиве

Последний аргумент функции TEXTSPLIT, pad_with, полезен в случае, если одно или несколько значений отсутствуют в исходной строке. Когда такая строка разбивается на столбцы и строки, по умолчанию Excel возвращает ошибку #N/A вместо отсутствующих значений, чтобы не нарушить структуру двумерного массива.

В приведенной ниже строке отсутствует символ "=" (col_delimiter) после слова "Score". Для сохранения целостности полученного массива функция TEXTSPLIT выводит ошибку #N/A рядом со словом "Score".

Чтобы сделать результат более удобным для пользователя, можно заменить ошибку #N/A на любое значение. Просто укажите желаемое значение в аргументе pad_with.

В нашем случае это может быть дефис ("-"):

=TEXTSPLIT(A2; "=", ", ", , ,"-")

Или же пустая строка (""):

=TEXTSPLIT(A2; "=", ", ", , ,"")

Разделение дат на день, месяц и год

Для разделения даты на отдельные части необходимо сначала преобразовать дату в текст, так как функция TEXTSPLIT работает только с текстовыми строками, а даты в Excel представлены числами.

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

В нашем случае формула будет выглядеть так:

=TEXT(A2; "m/d/yyyy")

Следующим шагом нужно вложить указанную выше функцию в первый аргумент TEXTSPLIT и ввести соответствующий разделитель для второго или третьего аргумента, в зависимости от того, хотите ли вы разделить данные по столбцам или строкам. В данном примере части даты разделяются косыми чертами, поэтому мы используем "/" для аргумента col_delimiter:

=TEXTSPLIT(TEXT(A2; "m/d/yyyy"); "/")

Разделение дат на день, месяц и год выполнено.

Разделение ячеек и удаление определенных символов

Представьте, что вы разделили длинную строку на части, но в полученном массиве остались нежелательные символы, такие как скобки.

Чтобы удалить открывающие и закрывающие скобки сразу, вложите две функции SUBSTITUTE одну в другую (каждая из которых заменяет одну скобку пустой строкой) и используйте формулу TEXTSPLIT для текстового аргумента внутренней функции SUBSTITUTE:

=SUBSTITUTE(SUBSTITUTE(TEXTSPLIT(A2; " ", "; "); "(", ""), ")", "")

Теперь ненужные символы удалены.

Пропуск значений при разделении строк

Предположим, что вам нужно разделить строки на четыре столбца: Имя, Фамилия, Оценка и Результат. Проблема заключается в том, что некоторые строки содержат титулы "Mr." или "Ms.", из-за чего результат получается неправильным.

Решение может показаться неочевидным, но оно на самом деле очень простое :)

Помимо существующих разделителей, таких как пробел (" ") и запятая с пробелом (", "), вы включаете строки "Mr. " и "Ms. " в массив разделителей (col_delimiter), чтобы функция использовала эти титулы для разделения текста. Чтобы игнорировать пустые значения, установите аргумент ignore_empty в TRUE.

=TEXTSPLIT(A2; {" ", ", ", "Mr. ", "Ms. "}; , TRUE)

Теперь результаты абсолютно корректны!

Альтернативы TEXTSPLIT

В тех версиях Excel, где функция TEXTSPLIT не поддерживается, вы можете разделить строки, используя различные комбинации функций ПОИСК / НАЙТИ с ЛЕВСИМВ, ПРАВСИМВ и ПСТР.

Имейте в виду, что в отличие от динамической функции TEXTSPLIT, эти формулы следуют традиционному принципу "одна формула — одна ячейка". Вы вводите формулу в первую ячейку, а затем протягиваете её вниз по столбцу для копирования в нижние ячейки.