Обща формула
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Обобщение
За да разделите текст с разделител и да преобразувате резултата в масив, можете да използвате функцията FILTERXML с помощта на функциите SUBSTITUTE и TRANSPOSE. В показания пример формулата в 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 тук:
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.
