Generische Formel
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Zusammenfassung
Um Text mit einem Trennzeichen zu teilen und das Ergebnis in ein Array umzuwandeln, können Sie die Funktion FILTERXML mithilfe der Funktionen SUBSTITUTE und TRANSPOSE verwenden. In dem gezeigten Beispiel lautet die Formel in D5:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Hinweis: FILTERXML ist in Excel auf dem Mac oder in Excel Online nicht verfügbar.
Hinweis: Ich habe diesen Trick von Bill Jelen in einem MrExcel-Video gelernt.
Erläuterung
Excel verfügt nicht über eine Funktion zum Aufteilen von Text in ein Array, ähnlich der PHP-Explosionsfunktion oder der Python-Aufteilungsmethode. Um dieses Problem zu umgehen, können Sie die FILTERXML-Funktion verwenden, nachdem Sie dem Text zuerst ein XML-Markup hinzugefügt haben.
In dem gezeigten Beispiel haben wir mehrere durch Kommas getrennte Textzeichenfolgen wie diese:
"Jim,Brown,33,Seattle,WA"
Ziel ist es, die Informationen unter Verwendung des Kommas als Trennzeichen in separate Spalten aufzuteilen.
Die erste Aufgabe besteht darin, diesem Text ein XML-Markup hinzuzufügen, damit er mit der Funktion FILTERXML als XML analysiert werden kann. Wir werden jedes Feld im Text willkürlich zu einem Element machen, das von einem übergeordneten Element eingeschlossen wird. Wir beginnen hier mit der SUBSTITUTE-Funktion:
SUBSTITUTE(B5,",","")
Das Ergebnis von SUBSTITUTE ist eine Textzeichenfolge wie folgt:
"JimBrown33SeattleWA"
Um wohlgeformte XML-Tags sicherzustellen und alle Elemente in ein übergeordnetes Element zu verpacken, stellen wir weitere XML-Tags wie folgt voran und hängen sie an:
""&SUBSTITUTE(B5,",","")&""
Dies ergibt eine Textzeichenfolge wie diese (Zeilenumbrüche zur besseren Lesbarkeit hinzugefügt)
" Jim Brown 33 Seattle WA "
Dieser Text wird direkt an die FILTERXML-Funktion als XML-Argument mit dem Xpath-Ausdruck "// y" übergeben:
FILTERXML("JimBrown33SeattleWA","//y")
Xpath ist eine Parsing-Sprache und "// y" wählt alle Elemente aus. Das Ergebnis von FILTERXML ist ein vertikales Array wie folgt:
("Jim";"Brown";33;"Seattle";"WA")
Da wir in diesem Fall ein horizontales Array wünschen, wickeln wir die TRANSPOSE-Funktion um FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Das Ergebnis ist ein horizontales Array wie folgt:
("Jim","Brown",33,"Seattle","WA")
Dies geht in den Bereich D5: H5 in Excel 365 über.
