Generička formula
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Sažetak
Da biste tekst podijelili graničnikom i rezultat pretvorili u niz, možete upotrijebiti funkciju FILTERXML uz pomoć funkcija ZAMJENA i TRANZOS. U prikazanom primjeru formula u D5 je:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Napomena: FILTERXML nije dostupan u programu Excel na Macu ili u programu Excel Online.
Napomena: Ovaj trik naučio sam od Billa Jelena u videu MrExcel.
Obrazloženje
Excel nema funkciju posvećenu dijeljenju teksta na niz, slično funkciji PHP explode ili Python split metodi. Kao zaobilazno rješenje možete koristiti funkciju FILTERXML nakon što ste tekstu dodali XML oznaku.
U prikazanom primjeru imamo nekoliko tekstualnih nizova razdvojenih zarezom poput ovog:
"Jim,Brown,33,Seattle,WA"
Cilj je podijeliti informacije u zasebne stupce pomoću zareza kao graničnika.
Prvi je zadatak dodati XML oznaku ovom tekstu kako bi se mogao raščlaniti kao XML s funkcijom FILTERXML. Svako polje u tekstu proizvoljno ćemo učiniti elementom zatvorenim nadređenim elementom. Ovdje započinjemo s funkcijom ZAMJENA:
SUBSTITUTE(B5,",","")
Rezultat iz SUBSTITUTE je tekstualni niz poput ovog:
"JimBrown33SeattleWA"
Da bismo osigurali dobro oblikovane XML oznake i da bismo sve elemente umotali u nadređeni element, dodajemo i dodajemo još XML oznaka poput ove:
""&SUBSTITUTE(B5,",","")&""
Ovo daje tekstualni niz poput ovog (dodani su prijelomi redaka radi čitljivosti)
" Jim Brown 33 Seattle WA "
Ovaj se tekst isporučuje izravno funkciji FILTERXML kao xml argument, s Xpath izrazom "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath je jezik za raščlanjivanje i "// y" odabire sve elemente. Rezultat iz FILTERXML vertikalni je niz poput ovog:
("Jim";"Brown";33;"Seattle";"WA")
Budući da u ovom slučaju želimo horizontalni niz, omotavamo funkciju TRANSPOSE oko FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Rezultat je vodoravni niz poput ovog:
("Jim","Brown",33,"Seattle","WA")
koja se u programu Excel 365 prelijeva u raspon D5: H5.
