Общая формула
=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))
Резюме
Чтобы разделить текст по произвольному разделителю (запятая, пробел, вертикальная черта и т. Д.), Вы можете использовать формулу, основанную на функциях ОБРЕЗАТЬ, СРЕДНЕЕ, ПОДСТАВИТЬ, ПОВТОР и LEN. В показанном примере формула C5 имеет следующий вид:
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))
Примечание: ссылки на B5 и C4 являются смешанными ссылками, позволяющими копировать формулу поперек и вниз.
Объяснение
Суть этой формулы состоит в том, чтобы заменить заданный разделитель большим количеством пробелов с помощью SUBSTITUTE и REPT, затем использовать функцию MID для извлечения текста, относящегося к «n-му вхождению», и функцию TRIM, чтобы избавиться от лишнего пробела.
В этом фрагменте разделитель (delim) заменен количеством пробелов, равным общей длине строки:
SUBSTITUTE(A1,delim,REPT(" ",LEN(A1)))
Затем формула использует функцию MID для извлечения n-й подстроки. Начальная точка рассчитывается с помощью приведенного ниже кода, где N представляет собой "nth":
(N-1)*LEN(A1)+1
Общее количество извлеченных символов равно длине полной текстовой строки. Затем функция TRIM удаляет все лишние пробелы и возвращает только n-ю строку.
Извлечь только один экземпляр
Хотя пример настроен на извлечение 5 подстрок из текста в столбце B, вы можете легко извлечь только 1 экземпляр. Например, чтобы извлечь только 4-й элемент (город), вы можете использовать:
=TRIM(MID(SUBSTITUTE(B5,"|",REPT(" ",LEN(B5))),(4-1)*LEN(B5)+1,LEN(B5)))
Функция текста в столбцы
Для ручного одноразового преобразования Excel имеет встроенную функцию под названием «Текст в столбцы», которая может разбивать текст в ячейках с помощью разделителя по вашему выбору. Вы найдете эту функцию на вкладке «Данные» на ленте в разделе «Инструменты для работы с данными».
