Generisk formel
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Sammanfattning
För att dela text med en avgränsare och omvandla resultatet till en matris kan du använda FILTERXML-funktionen med hjälp av SUBSTITUTE- och TRANSPOSE-funktionerna. I exemplet som visas är formeln i D5:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Obs! FILTERXML är inte tillgängligt i Excel på Mac eller i Excel Online.
Obs: Jag lärde mig detta trick från Bill Jelen i en MrExcel-video.
Förklaring
Excel har ingen funktion som är avsedd för att dela text till en matris, som liknar PHP-exploderingsfunktionen eller Python-delningsmetoden. Som en lösning kan du använda FILTERXML-funktionen efter att du först lagt till XML-markering i texten.
I exemplet som visas har vi flera kommaavgränsade textsträngar så här:
"Jim,Brown,33,Seattle,WA"
Målet är att dela upp informationen i separata kolumner med kommatecken som avgränsare.
Den första uppgiften är att lägga till XML-markering i denna text så att den kan analyseras som XML med FILTERXML-funktionen. Vi ska godtyckligt göra varje fält i texten till ett element, omslutet med ett överordnat element. Vi börjar med SUBSTITUTE-funktionen här:
SUBSTITUTE(B5,",","")
Resultatet från SUBSTITUTE är en textsträng så här:
"JimBrown33SeattleWA"
För att säkerställa välformade XML-taggar och för att radera alla element i ett överordnat element, förbereder vi och lägger till fler XML-taggar så här:
""&SUBSTITUTE(B5,",","")&""
Detta ger en textsträng som denna (radbrytningar läggs till för läsbarhet)
" Jim Brown 33 Seattle WA "
Denna text levereras direkt till FILTERXML-funktionen som XML-argument, med ett Xpath-uttryck av "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath är ett analyseringsspråk och "// y" markerar alla element. Resultatet från FILTERXML är en vertikal matris så här:
("Jim";"Brown";33;"Seattle";"WA")
Eftersom vi vill ha en horisontell matris i det här fallet slår vi in TRANSPOSE-funktionen runt FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Resultatet är en horisontell matris så här:
("Jim","Brown",33,"Seattle","WA")
som spelas in i intervallet D5: H5 i Excel 365.
