XMLFILTERN zum Aufteilen von Text verwenden

Ich bin bei der TechCommunity im Forum Microsoft Localization recht aktiv und melde dort z.B. Übersetzungsfehler zu den einzelnen Office-Produkten oder Microsoft 365. Nun besteht die TechCommunity nicht nur aus diesem Forum, sondern auch Excel ist dort vertreten. Sergei Baklan, ein sehr geschätzter Excel MVP-Kollege aus Russland hatte dort in einem Beitrag eine sehr interessante Möglichkeit gefunden, wie ein durch ein Trennzeichen zusammengesetzter Text anhand der Excel-Funktion XMLFILTERN wieder aufgeteilt werden kann. Voraussetzung dazu ist allerdings, dass Excel Dynamische Arrays beherrscht. Dies ist nur in neueren Excel-Versionen verfügbar.

Die Funktion XMLFILTERN

Die Funktion XMLFILTERN liefert aus XML-Daten einen Wert zurück und bedient sich dabei der XPath-Syntax. Sofern die XML-Daten valide sind, stellt sich somit eine recht einfache Möglichkeit dar, auf Elemente der XML-Struktur zuzugreifen.

Nehmen wir mal an, folgende XML-Struktur für eine Liste von Büchern wäre gegeben.

Um per XMLFITERN die Kategorie zum zweiten Buch zu extrahieren, müsste der Pfad in der XPath-Schreibweise //buecherliste/buch[2]/kategorie angesprochen werden. Eine sehr gute Einführung zu XPath findet sich bei SelfHTML. Angenommen, der XML-Code befindet sich in Zelle A1, würde die Excel-Formel lauten XMLFILTERN(A1;“//buecherliste/buch[2]/kategorie“). Als Ergebnis erhalten wir dann PowerPoint.

Als Quelle für das erste Argument der Excel-Funktion – der XML-Code – kann der Inhalt einer Zelle oder auch z.B. eine Online-Quelle via der Excel-Funktion WEBDIENST verwendet werden. Nichts hindert uns aber auch daran, den XML-Code dynamisch aufzubauen, wie wir später sehen werden.

Vereinfachen wir aber zunächst das Beispiel, indem wir nur eine Ebene innerhalb der XML-Struktur verwenden. Dies könnte wie folgt aussehen.

Brechen wir dies noch weiter runter, indem wir nur noch X und Y als XML-Tags verwenden. Wir erhalten somit die kürzeste Form für die XML-Struktur.

Die Funktion XMLFILTERN und dynamische Arrays

Seit einiger Zeit sind in modernen Excel-Versionen – also Excel 365 – sogenannte dynamische Arrays enthalten. Diese lassen Matrixformeln überlaufen – das heißt, dass Formeln die mehr als den Inhalt einer Zelle zurückgeben können, automatisch auf Nachbarzellen expandieren. Parallel zur Einführung von dynamischen Arrays wurden neue mächtige Excel-Funktionen, wie FILTER, SORTIEREN oder EINDEUTIG eingeführt. Ich habe diese neue Funktionalität in meinem Artikel zur Ermittlung eindeutiger Werte in einem Bereich etwas detaillierter beschrieben. Aber auch andere Autoren, wie z.B. Martin Weiß haben sich dieses Themas angenommen.

Wie verhält es sich nun bei der Funktion XMLFILTERN? Dazu verändern wir nun die Formel, indem wir nicht mehr explizit eines der Elemente zum Tag Y ansprechen, sondern einfach nur //x/y schreiben. Hier kommt nun das dynamische Array zum Tragen und die Formel expandiert bzw. läuft in Zelle A3 über.

Nichts hindert uns daran, den Wert aus A1 direkt als Zeichenkette in die Formel zu schreiben. Das sieht dann wie folgt aus.

Ich vermute, Sie ahnen es schon: Jenny und Max könnten sich nun in zwei Zellen befinden und statt statisch in der Formel als Bezug angegeben werden.

Text mit XMLFILTERN aufteilen

Ziel ist es jedoch einen Text aufzuteilen, der durch ein Trennzeichen getrennt ist. Angenommen, dieses Zeichen wäre ein Strichpunkt, stände in A1 der Text Jenny;Max.

Wir können uns nun den Text so umbauen, dass der Strichpunkt durch XML-Tags ersetzt wird und die weiteren fehlenden XML-Tags um den Text herumgelegt werden. Zum Ersetzen des Strichpunkts verwenden wir die Funktion WECHSELN und setzen noch XML-Tags vor und hinter dem Funktionsaufruf, was dann wie folgt aussieht:

=“„&WECHSELN(A1;“;“;“„)&“„

Anschließend kapseln wir das Konstrukt in der Funktion XMLFILTERN und haben somit den Text in seine Einzelteile als dynamisches Array zerlegt.

Möchten wir die Einzelemente nicht in Zeilen untereinander darstellen, sondern nebeneinander, können wir das Konstrukt innerhalb der Funktion MTRANS kapseln, was das Ergebnis von XMLFILTERN transponiert.

Fazit

Gerade jetzt, wo dynamische Arrays in Excel eingeführt wurden, wäre natürlich etwas einfacher und komfortabler, wenn Excel eine native Funktion zum Aufteilen von Texten zur Verfügung stellen würde. Was ich mir auch wünschen würde – und sicherlich nicht nur ich.

Die hier vorgestellte Methode ist letztlich nur ein Hilfskonstrukt, das aber durchaus in einigen Fällen sicherlich sinnvoll zur Anwendung kommen kann.

Happy Exceling :-)