Generisk formel
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Sammendrag
For å dele tekst med en avgrenser og transformere resultatet til en matrise, kan du bruke FILTERXML-funksjonen med hjelp fra SUBSTITUTE og TRANSPOSE-funksjonene. I eksemplet vist er formelen i D5:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Merk: FILTERXML er ikke tilgjengelig i Excel på Mac eller i Excel Online.
Merk: Jeg lærte dette trikset av Bill Jelen i en MrExcel-video.
Forklaring
Excel har ikke en funksjon dedikert til å dele tekst til en matrise, som ligner på PHP-eksploderingsfunksjonen, eller Python split-metoden. Som en løsning kan du bruke FILTERXML-funksjonen etter at du først har lagt til XML-markering i teksten.
I eksemplet som vises har vi flere kommaavgrensede tekststrenger som dette:
"Jim,Brown,33,Seattle,WA"
Målet er å dele informasjonen i separate kolonner ved å bruke kommaet som skilletegn.
Den første oppgaven er å legge til XML-markering i denne teksten, slik at den kan analyseres som XML med FILTERXML-funksjonen. Vi skal vilkårlig gjøre hvert felt i teksten til et element, innelukket med et overordnet element. Vi starter med SUBSTITUTE-funksjonen her:
SUBSTITUTE(B5,",","")
Resultatet fra SUBSTITUTE er en tekststreng som denne:
"JimBrown33SeattleWA"
For å sikre velformede XML-koder og for å pakke alle elementene i et overordnet element, legger vi flere XML-koder som dette til og legger til:
""&SUBSTITUTE(B5,",","")&""
Dette gir en tekststreng som dette (linjeskift lagt til for lesbarhet)
" Jim Brown 33 Seattle WA "
Denne teksten leveres direkte til FILTERXML-funksjonen som xml-argument, med et Xpath-uttrykk for "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath er et analysespråk og "// y" velger alle elementene. Resultatet fra FILTERXML er en vertikal matrise som dette:
("Jim";"Brown";33;"Seattle";"WA")
Fordi vi vil ha en horisontal matrise i dette tilfellet, pakker vi TRANSPOSE-funksjonen rundt FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Resultatet er en horisontal matrise som dette:
("Jim","Brown",33,"Seattle","WA")
som går ut i området D5: H5 i Excel 365.
