Загальна формула
=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))
Резюме
Щоб розділити текст на довільний роздільник (кома, пробіл, контур тощо), ви можете використовувати формулу на основі функцій TRIM, MID, SUBSTITUTE, REPT та LEN. У наведеному прикладі формула в С5 має вигляд:
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))
Примітка: посилання на B5 і C4 - це змішані посилання, що дозволяють копіювати формулу поперек і вниз.
Пояснення
Основна суть цієї формули полягає в тому, щоб замінити заданий роздільник великою кількістю пробілів за допомогою SUBSTITUTE і REPT, а потім використовувати функцію MID для вилучення тексту, пов’язаного з „n-м випадком”, та функції TRIM, щоб позбутися зайвого місця.
У цьому фрагменті роздільник (роздільник) замінюється на кількість пробілів, що дорівнює загальній довжині рядка:
SUBSTITUTE(A1,delim,REPT(" ",LEN(A1)))
Потім формула використовує функцію MID для вилучення n-го підрядка. Початкова точка обчислюється за кодом нижче, де N означає "n-ту":
(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 має вбудовану функцію під назвою "Текст у стовпці", яка може розділяти текст на клітинки з роздільником на ваш вибір. Ви знайдете цю функцію на вкладці Дані стрічки в розділі Інструменти даних.
