Všeobecný vzorec
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Zhrnutie
Ak chcete rozdeliť text pomocou oddeľovača a výsledok transformovať do poľa, môžete použiť funkciu FILTERXML s pomocou funkcií SUBSTITUTE a TRANSPOSE. V zobrazenom príklade je vzorec v D5:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Poznámka: FILTERXML nie je k dispozícii v programe Excel na počítačoch Mac alebo Excel Online.
Poznámka: Tento trik som sa naučil od Billa Jelena vo videu MrExcel.
Vysvetlenie
Excel nemá funkciu vyhradenú na rozdelenie textu na pole, podobnú funkcii explodovania PHP alebo metóde rozdelenia Pythonu. Ako riešenie môžete použiť funkciu FILTERXML po prvom pridaní značiek XML do textu.
V zobrazenom príklade máme niekoľko textových reťazcov oddelených čiarkami, ako je tento:
"Jim,Brown,33,Seattle,WA"
Cieľom je rozdeliť informácie do samostatných stĺpcov pomocou čiarky ako oddeľovača.
Prvou úlohou je pridať do tohto textu značky XML, aby ich bolo možné analyzovať ako XML pomocou funkcie FILTERXML. Z každého poľa v texte urobíme ľubovoľný prvok uzavretý s nadradeným prvkom. Tu začíname funkciou SUBSTITUTE:
SUBSTITUTE(B5,",","")
Výsledkom zo SUBSTITUTE je textový reťazec, ako je tento:
"JimBrown33SeattleWA"
Aby sme zaistili správne tvarované značky XML a zabalili všetky prvky do nadradeného prvku, pripravíme a pripojíme ďalšie značky XML, napríklad takto:
""&SUBSTITUTE(B5,",","")&""
Takto sa získa takýto textový reťazec (kvôli čitateľnosti sú pridané zalomenia riadkov)
" Jim Brown 33 Seattle WA "
Tento text sa dodáva priamo do funkcie FILTERXML ako argument xml s výrazom Xpath „// y“:
FILTERXML("JimBrown33SeattleWA","//y")
Xpath je jazyk syntaktickej analýzy a znak „// y“ vyberie všetky prvky. Výsledkom z FILTERXML je vertikálne pole, ako je toto:
("Jim";"Brown";33;"Seattle";"WA")
Pretože v tomto prípade chceme horizontálne pole, zabalíme funkciu TRANSPOSE okolo FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Výsledkom je horizontálne pole, ako je toto:
("Jim","Brown",33,"Seattle","WA")
ktorý sa v aplikácii Excel 365 rozlieva do rozsahu D5: H5.
