Generisk formel
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Resumé
For at opdele tekst med en afgrænser og omdanne resultatet til en matrix kan du bruge FILTERXML-funktionen med hjælp fra SUBSTITUTE- og TRANSPOSE-funktionerne. I det viste eksempel er formlen i D5:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Bemærk: FILTERXML er ikke tilgængelig i Excel på Mac eller i Excel Online.
Bemærk: Jeg lærte dette trick af Bill Jelen i en MrExcel-video.
Forklaring
Excel har ikke en funktion dedikeret til opdeling af tekst til et array, der ligner PHP-eksploderingsfunktionen eller Python-splitmetoden. Som en løsning kan du bruge FILTERXML-funktionen efter først at have tilføjet XML-markering til teksten.
I det viste eksempel har vi flere komma-afgrænsede tekststrenge som denne:
"Jim,Brown,33,Seattle,WA"
Målet er at opdele oplysningerne i separate kolonner ved hjælp af kommaet som en afgrænser.
Den første opgave er at tilføje XML-markering til denne tekst, så den kan parses som XML med FILTERXML-funktionen. Vi vil vilkårligt gøre hvert felt i teksten til et element, lukket med et overordnet element. Vi starter med SUBSTITUTE-funktionen her:
SUBSTITUTE(B5,",","")
Resultatet fra SUBSTITUTE er en tekststreng som denne:
"JimBrown33SeattleWA"
For at sikre velformede XML-tags og indpakke alle elementer i et overordnet element forbereder vi og tilføjer flere XML-tags som dette:
""&SUBSTITUTE(B5,",","")&""
Dette giver en tekststreng som denne (linjeskift tilføjet for læsbarhed)
" Jim Brown 33 Seattle WA "
Denne tekst leveres direkte til FILTERXML-funktionen som xml-argument med et Xpath-udtryk på "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath er et parsingssprog, og "// y" vælger alle elementer. Resultatet fra FILTERXML er et lodret array som dette:
("Jim";"Brown";33;"Seattle";"WA")
Fordi vi ønsker en vandret matrix i dette tilfælde, pakker vi TRANSPOSE-funktionen omkring FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Resultatet er et vandret array som dette:
("Jim","Brown",33,"Seattle","WA")
der spilder ind i området D5: H5 i Excel 365.
