Загальна формула
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Резюме
Щоб розділити текст з роздільником і перетворити результат на масив, ви можете скористатися функцією FILTERXML за допомогою функцій ЗАМІНИТЬ і ТРАНЗОЗ. У наведеному прикладі формула в D5 має вигляд:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Примітка: FILTERXML недоступний у програмі Excel на Mac або в програмі Excel Online.
Примітка: Я дізнався цей фокус від Білла Джелена у відео MrExcel.
Пояснення
Excel не має функції, призначеної для розділення тексту на масив, подібно до функції PHP explode або методу розділення Python. Як обхідний шлях можна використовувати функцію FILTERXML після першого додавання розмітки XML до тексту.
У наведеному прикладі ми маємо кілька текстових рядків, розділених комами, таких як:
"Jim,Brown,33,Seattle,WA"
Мета - розділити інформацію на окремі стовпці, використовуючи кому як роздільник.
Перше завдання - додати розмітку XML до цього тексту, щоб його можна було проаналізувати як XML за допомогою функції FILTERXML. Ми довільно зробимо кожне поле в тексті елементом, укладеним батьківським елементом. Ми починаємо з функції ЗАМІНИТИ тут:
SUBSTITUTE(B5,",","")
Результатом від SUBSTITUTE є текстовий рядок, такий:
"JimBrown33SeattleWA"
Щоб забезпечити добре сформовані теги XML та обернути всі елементи у батьківському елементі, ми додаємо та додаємо більше тегів XML, як це:
""&SUBSTITUTE(B5,",","")&""
З цього виходить такий текстовий рядок (додаються розриви рядків для читабельності)
" Jim Brown 33 Seattle WA "
Цей текст доставляється безпосередньо до функції FILTERXML як аргумент xml із виразом Xpath "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath - мова розбору, і "// y" виділяє всі елементи. Результатом FILTERXML є вертикальний масив, такий:
("Jim";"Brown";33;"Seattle";"WA")
Оскільки в цьому випадку нам потрібен горизонтальний масив, ми обертаємо функцію TRANSPOSE навколо FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Результат - горизонтальний масив, такий:
("Jim","Brown",33,"Seattle","WA")
який переходить у діапазон D5: H5 в Excel 365.
