Функция 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 используется для одной ячейки, он возвращает текст в одном массиве, а значения распределяются по листу в несколько ячеек. Однако, когда используется для диапазона ячеек, функция возвращает «массив массивов».