Функция TEXTSPLIT в Excel

Функция TEXTSPLIT в Excel разбивает текст по заданному разделителю на массив, который распределяется по нескольким ячейкам. ТЕКСТПЛИТ может разбивать текст на строки или столбцы.

Что делает функция TEXTSPLIT в Excel?

Разделить текстовую строку с помощью разделителя

Возвращаемое значение

Текст в нескольких ячейках

Аргументы

  • text — текстовая строка, которую нужно разделить.
  • col_delimiter — Символ(ы) для разделения столбцов.
  • row_delimiter — [необязательный] Символ(ы) для разделения строк.
  • ignore_empty — [необязательно] Игнорировать пустые значения. ИСТИНА = игнорировать, ЛОЖЬ = сохранять. По умолчанию — ЛОЖЬ.
  • match_mode — [необязательный] Учет регистра. 0 = включено, 1 = отключено. По умолчанию — 0.
  • Pad_with — [необязательно] Значение для заполнения отсутствующих значений в двумерных массивах.

Синтаксис

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [Pad_with])

Примечания по использованию

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

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with])

Текст  — текстовая строка, которую нужно разделить. Col_delimiter — это разделитель, используемый для разделения текста на столбцы, а  row_delimiter  — это разделитель, используемый для разделения текста на строки. Четвертый аргумент,  ignore_empty, управляет поведением TEXTSPLIT с пустыми значениями (т. е. между разделителями нет значений).

По умолчанию ignore_empty имеет значение FALSE и TEXTSPLIT не будет игнорировать пустые значения. На практике это означает, что вы увидите пустую ячейку на листе, когда в тексте есть пустое значение. Установите  ignore_empty  в значение TRUE, чтобы игнорировать пустые значения. Match_mode  определяет чувствительность к регистру при поиске разделителя.

По умолчанию ТЕКСТСПЛИТ чувствителен к регистру, а match_mode равен нулю (0). Поставьте 1, чтобы отключить  чувствительность к регистру. Последний аргумент,  pad_with, — это значение, возвращаемое, когда выходные данные TEXTSPLIT представляют собой 2D-массив и значения отсутствуют. Смотрите ниже для получения дополнительной информации.

Примечание. В Excel есть три функции, которые разделяют текстовое значение: TEXTBEFORE, TEXTAFTER и TEXTSPLIT. Используйте TEXTBEFORE для извлечения текста перед разделителем,  TEXTAFTER для извлечения текста после  разделителя и TEXTSPLIT для извлечения всего текста, разделенного разделителями.

Основное использование

Результатом TEXTSPLIT является массив, который может быть горизонтальным (столбцы) или вертикальным (строки). На листе ниже показаны оба варианта:

Первая формула в ячейке D3 разделяет три значения на отдельные столбцы:

=TEXTSPLIT(B3,",") // returns {"Red","Blue","Green"}

Обратите внимание, что в качестве разделителя col_delimiter используется запятая без пробела , заключенная в двойные кавычки («,»). Формула в ячейке D5 использует тот же разделитель для разделения текста на отдельные строки:

=TEXTSPLIT(B3,,",") // returns {"Red";"Blue";"Green"}

Во второй формуле разделитель («,») отображается в качестве третьего аргумента row_delimiter, а  col_delimiter не указывается.

Игнорирование пустых значений

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

Формула в ячейке D3 не содержит значения  ignore_empty, которое по умолчанию имеет значение FALSE:

=TEXTSPLIT(B3,",") // empty values not ignored

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

=TEXTSPLIT(B3,",",,TRUE) // ignore empty values

В этом случае TEXTSPLIT ведет себя так, как будто отсутствующего значения вообще не существует.

Примечание: вы можете использовать 1 и 0 вместо TRUE и FALSE для аргумента ignore_empty.

Режим соответствия

Пятый аргумент match_mode определяет чувствительность к регистру при поиске разделителя. По умолчанию TEXTSPLIT чувствителен к регистру, а match_mode равен нулю (0). Поставьте 1, чтобы отключить чувствительность к регистру. В примере ниже разделителями являются «x» и «X». Формула в D5 устанавливает режим соответствия на 1, чтобы TEXTSPLIT игнорировал регистр. В результате формула работает для обоих случаев:

=TEXTSPLIT(B5," x ",,,1)

Строки и столбцы

TEXTSPLIT может одновременно разбивать текст на строки и столбцы, как показано ниже:

В этом случае знак равенства («=») указывается в качестве разделителя_столбца, а запятая («,») — в качестве pad_with:

=TEXTSPLIT(B3,"=",",")

Результирующий массив из TEXTSPLIT содержит 3 строки и 2 столбца.

Заполнение

Последний аргумент в TEXTSPLIT — это Pad_with.  Этот аргумент является необязательным и по умолчанию имеет значение #N/A. Заполнение используется, когда выходные данные содержат строки и столбцы и отсутствует значение, которое могло бы повлиять на структуру массива. На листе ниже «Синий» не содержит количества (нет разделителя «=»). В результате TEXTSPLIT возвращает #N/A, куда должно идти количество, чтобы сохранить целостность массива.

В формуле в ячейке E3 не указан аргумент Pad_with , поэтому возвращается значение по умолчанию:

=TEXTSPLIT(B3,"=",",") // default padding is #N/A

В ячейке E7 для поля_pad_with указывается «x»,   поэтому в ячейке F8 вместо #N/A появляется «x».

=TEXTSPLIT(B3,"=",",",,"x")

Несколько разделителей

Несколько разделителей могут быть переданы в TEXTSPLIT в виде константы массива, например {«x»,»y»}, где x и y представляют собой разделители:

На приведенном выше листе текст в B3 разделен дефисами «-» и запятыми («,»). Формула в ячейке F3:

=TEXTSPLIT(B3,{"-",","})

Также обратите внимание, что между зеленым и фиолетовым есть дополнительное пространство. Функцию TRIM можно использовать для очистки лишних символов пробела, которые появляются в выводе TEXTSPLIT. Формула в F5:

=TRIM(TEXTSPLIT(B3,{"-",","}))

Обратите внимание, что дополнительное пространство, которое появляется перед тем, как фиолетовый цвет в ячейке I3 исчезнет в ячейке I5.

Массив массивов

При использовании TEXTSPLIT в эксель вы можете столкнуться с ограничением механизма формул Excel, когда формула не будет возвращать «массивы массивов». Когда TEXTSPLIT используется для одной ячейки, он возвращает текст в одном массиве, а значения распределяются по листу в несколько ячеек. Однако, когда используется для диапазона ячеек, функция возвращает «массив массивов».

Автор А. Днепров

Обучаю основам работы с офисными программами.

Функция TEXTSPLIT в Excel
15.4 Фигурная обрезка