Splošna formula
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Povzetek
Če želite besedilo razdeliti z ločilnikom in rezultat pretvoriti v matriko, lahko s pomočjo funkcij SUBSTITUTE in TRANSPOSE uporabite funkcijo FILTERXML. V prikazanem primeru je formula v D5:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Opomba: FILTERXML ni na voljo v Excelu na Macu ali v programu Excel Online.
Opomba: tega trika sem se naučil od Billa Jelena v videu MrExcel.
Pojasnilo
Excel nima funkcije, namenjene razdelitvi besedila na matriko, podobno kot funkcija PHP explode ali Python split metoda. Kot rešitev lahko uporabite funkcijo FILTERXML, potem ko ste besedilu najprej dodali oznake XML.
V prikazanem primeru imamo več besedilnih nizov, ločenih z vejico, kot je ta:
"Jim,Brown,33,Seattle,WA"
Cilj je razdeliti informacije v ločene stolpce z vejico kot ločilo.
Prva naloga je, da temu besedilu dodamo oznako XML, da jo lahko s funkcijo FILTERXML razčlenimo kot XML. Vsako polje v besedilu bomo poljubno naredili kot element, zaprt z nadrejenim elementom. Začnemo s funkcijo SUBSTITUTE tukaj:
SUBSTITUTE(B5,",","")
Rezultat SUBSTITUTE je besedilni niz, kot je ta:
"JimBrown33SeattleWA"
Da bi zagotovili dobro oblikovane oznake XML in zavili vse elemente v nadrejeni element, dodamo in dodamo več oznak XML, kot je ta:
""&SUBSTITUTE(B5,",","")&""
Tako dobimo takšen besedilni niz (dodani so prelomi vrstic zaradi berljivosti)
" Jim Brown 33 Seattle WA "
To besedilo je dostavljeno neposredno v funkcijo FILTERXML kot argument xml z izrazom Xpath "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath je jezik za razčlenjevanje in "// y" izbere vse elemente. Rezultat iz FILTERXML je navpična matrika, kot je ta:
("Jim";"Brown";33;"Seattle";"WA")
Ker želimo v tem primeru vodoravno matriko, funkcijo TRANSPOSE zavijemo okoli FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Rezultat je vodoravna matrika, kot je ta:
("Jim","Brown",33,"Seattle","WA")
ki se v Excelu 365 prelije v obseg D5: H5.
