Разделение текста с разделителем в ячейке Excel

Встроенная функция Excel «Текст по столбцам» действительно позволяет быстро разделить текст по указанному разделителю. Альтернативный подход — использование формулы. В этой статье приведено пошаговое руководство по разделению текста с разделителем с помощью комбинации функций СЖПРОБЕЛЫ, ПСТР, ПОДСТАВИТЬ, ПОВТОР и ДЛСТР в Excel.

Как разделить текст в ячейке по заданному разделителю в Excel?

Сначала подготовьте свои данные. Предположим, вы уже создали список текстовых строк в столбце B, как показано на скриншоте ниже. Кроме того, вам нужно создать три вспомогательных столбца, заполнив их числами 1, 2 и 3 соответственно.

Универсальная формула

=TRIM(MID(SUBSTITUTE(A1, delimiter,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))

Аргументы

Как использовать эту формулу?

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

2. Введите в неё приведённую ниже формулу и нажмите клавишу Enter.

=TRIM(MID(SUBSTITUTE($B3,"-",REPT(" ",LEN($B3))),(G$3-1)*LEN($B3)+1,LEN($B3)))

3. Выделите ячейку с результатом и перетащите маркер заполнения вправо на две соседние ячейки, чтобы получить второй и третий фрагменты текста.

4. Оставив эти три ячейки выделенными, перетащите маркер заполнения вниз, чтобы разделить остальные текстовые строки в той же таблице с использованием того же разделителя. См. скриншот:

Теперь каждая текстовая строка в указанных ячейках разбита по столбцам с помощью разделителя «-».

Как работает эта формула?

=TRIM(MID(SUBSTITUTE($B3,"-",REPT(" ",LEN($B3))),(G$3-1)*LEN($B3)+1,LEN($B3)))

1. SUBSTITUTE($B3,"-",REPT(" ",LEN($B3)))

• REPT(" ",LEN($B3))Функция ДЛСТР (LEN) возвращает общее количество символов в KTE-Jan-359 — в данном случае результат равен 11. Затем функция ПОВТОР (REPT) создаёт строку из 11 пробелов, то есть « » (11 пробелов в кавычках).
• SUBSTITUTE($B3,"-", " ")Функция ПОДСТАВИТЬ (SUBSTITUTE) заменяет каждый дефис «-» в текстовой строке «KTE-Jan-359» на 11 пробелов, в результате чего получается «KTE Jan 359».

2. MID("KTE Jan 359",(G$3-1)*LEN($B3)+1,LEN($B3))

• MID("KTE Jan 359",1,11): функция ПСТР (MID) возвращает 11 символов, начиная с первого символа строки «KTE Jan 359». Результат — «KTE Jan 359».
  Примечание: буква N ссылается на ячейку $B3, значение в которой равно 1; начальная позиция рассчитывается по формуле (G$3-1)*LEN($B3)+1 = 1.

3. TRIM("KTE "): функция СЖПРОБЕЛЫ удаляет все лишние пробелы из «KTE » и возвращает «KTE».

Связанные функции

Связанные формулы