Yleinen kaava
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Yhteenveto
Jos haluat jakaa tekstin erotinmerkillä ja muuttaa tuloksen matriisiksi, voit käyttää FILTERXML-toimintoa SUBSTITUTE- ja TRANSPOSE-toimintojen avulla. Esitetyssä esimerkissä kaava kohdassa D5 on:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Huomautus: FILTERXML ei ole käytettävissä Macin Excelissä tai Excel Onlinessa.
Huomaa: Opin tämän temppun Bill Jeleniltä MrExcel-videosta.
Selitys
Excelissä ei ole toimintoa, joka on tarkoitettu tekstin jakamiseen taulukoksi, samanlainen kuin PHP-räjähdystoiminto tai Python-jakomenetelmä. Kiertotapana voit käyttää FILTERXML-funktiota, kun olet ensin lisännyt XML-merkinnät tekstiin.
Esitetyssä esimerkissä meillä on useita pilkulla erotettuja tekstimerkkijonoja:
"Jim,Brown,33,Seattle,WA"
Tavoitteena on jakaa tiedot erillisiin sarakkeisiin pilkulla erottimena.
Ensimmäinen tehtävä on lisätä XML-merkintä tähän tekstiin, jotta se voidaan jäsentää XML-muodossa FILTERXML-funktiolla. Teemme mielivaltaisesti jokaisesta tekstikentästä elementin, joka on suljettu pääelementin kanssa. Aloitamme SUBSTITUTE-toiminnolla tässä:
SUBSTITUTE(B5,",","")
Kohteen SUBSTITUTE tulos on seuraavanlainen tekstimerkkijono:
"JimBrown33SeattleWA"
Huolellisesti muotoiltujen XML-tunnisteiden varmistamiseksi ja kaikkien elementtien käärimiseksi pääelementtiin, olemme valmiit ja liittäneet lisää XML-tunnisteita seuraavasti:
""&SUBSTITUTE(B5,",","")&""
Tämä antaa tällaisen tekstimerkkijonon (rivinvaihdot lisätään luettavuuden vuoksi)
" Jim Brown 33 Seattle WA "
Tämä teksti toimitetaan suoraan FILTERXML-funktioon xml-argumenttina Xpath-lausekkeella "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath on jäsentävä kieli ja "// y" valitsee kaikki elementit. FILTERXML: n tulos on tällainen pystysuora taulukko:
("Jim";"Brown";33;"Seattle";"WA")
Koska haluamme vaakasuuntaisen taulukon tässä tapauksessa, käärimme TRANSPOSE-funktion FILTERXML: n ympärille:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Tuloksena on vaakasuuntainen taulukko:
("Jim","Brown",33,"Seattle","WA")
joka valuu alueelle D5: H5 Excel 365: ssä.
