Generieke formule
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Samenvatting
Om tekst te splitsen met een scheidingsteken en het resultaat om te zetten in een array, kunt u de functie FILTERXML gebruiken met behulp van de functies SUBSTITUTE en TRANSPOSE. In het getoonde voorbeeld is de formule in D5:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Opmerking: FILTERXML is niet beschikbaar in Excel op de Mac of in Excel Online.
Opmerking: ik heb deze truc van Bill Jelen geleerd in een MrExcel-video.
Uitleg
Excel heeft geen functie voor het splitsen van tekst in een array, vergelijkbaar met de PHP-explodeerfunctie of de Python-splitsingsmethode. Als tijdelijke oplossing kunt u de functie FILTERXML gebruiken, nadat u eerst XML-opmaak aan de tekst hebt toegevoegd.
In het getoonde voorbeeld hebben we verschillende door komma's gescheiden tekstreeksen zoals deze:
"Jim,Brown,33,Seattle,WA"
Het doel is om de informatie op te splitsen in afzonderlijke kolommen met de komma als scheidingsteken.
De eerste taak is om XML-markup aan deze tekst toe te voegen, zodat deze kan worden geparseerd als XML met de FILTERXML-functie. We gaan willekeurig van elk veld in de tekst een element maken, omsloten door een bovenliggend element. We beginnen hier met de SUBSTITUTE-functie:
SUBSTITUTE(B5,",","")
Het resultaat van SUBSTITUTE is een tekstreeks zoals deze:
"JimBrown33SeattleWA"
Om ervoor te zorgen dat de XML-tags correct zijn en om alle elementen in een bovenliggend element te verpakken, voegen we meer XML-tags toe en voegen ze als volgt toe:
""&SUBSTITUTE(B5,",","")&""
Dit levert een tekstreeks als deze op (regeleinden toegevoegd voor leesbaarheid)
" Jim Brown 33 Seattle WA "
Deze tekst wordt rechtstreeks aan de FILTERXML-functie geleverd als het xml-argument, met een Xpath-expressie van "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath is een parseertaal en "// y" selecteert alle elementen. Het resultaat van FILTERXML is een verticale array zoals deze:
("Jim";"Brown";33;"Seattle";"WA")
Omdat we in dit geval een horizontale array willen, wikkelen we de functie TRANSPONEREN rond FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Het resultaat is een horizontale array zoals deze:
("Jim","Brown",33,"Seattle","WA")
die in Excel 365 in het bereik D5: H5 terechtkomt.
