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

На самом деле, встроенная функция «Текст по столбцам» в Excel может помочь вам быстро разделить текст с определённым разделителем. Другой вариант — применить формулу для достижения этой цели. В этой статье мы предоставляем пошаговое руководство, чтобы помочь вам разделить текст с разделителем, используя комбинацию функций TRIM, MID, SUBSTITUTE, REPT и LEN в 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 ".
  Примечание: Буква N относится к $B3, а значение в $B3 равно 1, здесь начальная точка рассчитывается как (G$3-1)*LEN($B3)+1 = 1.

3. TRIM("KTE "): Функция TRIM удаляет все лишние пробелы из "KTE " и, наконец, возвращает результат как KTE.

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

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