Общая формула
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Резюме
Чтобы разделить текст с помощью разделителя и преобразовать результат в массив, вы можете использовать функцию FILTERXML с помощью функций SUBSTITUTE и TRANSPOSE. В показанном примере формула в D5:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Примечание. FILTERXML недоступен в Excel на Mac или в Excel Online.
Примечание: я узнал об этом трюке от Билла Джелен в видео MrExcel.
Объяснение
В Excel нет функции, предназначенной для разделения текста на массив, аналогичной функции разнесения PHP или методу разделения 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.
