TEXT.SPLIT

Nachdem ich mir nun einige benutzerdefinierte Array-Funktionen mithilfe der LAMBDA-Funktion in Excel erstellt habe, wird es Zeit, dass diese langsam auch zum Einsatz kommen. Die nachfolgend vorgestellte benutzerdefinierte Funktion TEXT.SPLIT verwendet ARRAY.FROMTEXT als Hilfsfunktion, um aus einem Text ein Array zu generieren.

Im Netz kursieren bereits einige LAMBDA-Funktionen, die einen Text anhand eines Trennzeichens aufteilen. Diese Lösungen berücksichtigen meines Wissens bislang jedoch nur ein Trennzeichen. TEXT.SPLIT ist in hingegegen der Lage, mehrere Trennzeichen zu berücksichtigen.

Die Funktion LAMBDA, auf der nachfolgende Formeln beruhen, wurde erst kürzlich in Excel eingeführt und ist derzeit nur in der Insider-Version von Excel verfügbar. Eine Einführung zu der LAMBDA-Funktion ist hier zu finden. Nebenbei, jeder kann Office-Insider werden, hier gibt es weitere Informationen zum Office-Insider-Programm.

Anforderungen

Die Funktion TEXT.SPLIT soll einen Text, der als Argument oder Bezug übergeben wird, anhand einer Liste von Trennzeichen in Teiltexte aufteilen, die ebenfalls als Text oder Bezug übergeben werden kann. Die Syntax der Funktion wäre somit:

=TEXT.SPLIT(Value;Separators)

Einige Anwendungsbeispiele sind:

=TEXT.SPLIT(A2;{" ";"-"})
=TEXT.SPLIT(A3;"-")
=TEXT.SPLIT(A4;MTRANS(K4:L4))
=TEXT.SPLIT(A5;K4:K5)

TEXT.SPLIT

Die Funktion LET ermöglicht es, Zwischenberechnungen in Variablen abzulegen und fördert, gerade bei komplexeren Formeln, die Lesbarkeit. Zudem kann durch das Verwenden der LET-Funktion ein Geschwindigkeitsvorteil entstehen, denn in Variablen abgelegt Formeln werden nur einmal berechnet. Die LET-Formel wird später in die LAMBDA-Funktion gekapselt. Eine Pseudoformel für TEXT.SPLIT sieht wie folgt aus:

=LET(Value;A2;
     Separators;{"@";"."};
     S;Validierung von Separators;
     T;Validierung von S;
     A;Erstellung eines Arrays aus dem Text;
     R;Hilfsvariable für Zeilen;
     C;Hilfsvariable für Spalten;
     M;Hilfsmatrix zum Vergleich;
     P;Hilfsvariable zu den Startpositionen;
     F;Filter;
     L;Hilfsvariable für Teillängen;
       Berechnung vom Ergebnis)

Für nachfolgende Erläuterungen konzentrieren wir uns auf ein einfaches Beispiel: es soll eine E-Mail-Adresse in Username, Domainname und TLD aufgeteilt werden. In folgender Abbildung ist das erwartetete Ergebnis in B2:D2 zu sehen.

Bauen wir nun die LET-Formel Stück für Stück auf. Da die Trennzeichen als Bezug übergeben werden können und diese für weitere Berechnungen als vertikale Liste vorliegen sollen, ist zunächst zu überprüfen, ob die Trennzeichenliste auch diese Bedingungen erfüllt. Für S ergibt sich somit:

S=WENN(SPALTEN(Separators)>1;
  MTRANS(INDEX(Separators;1;0));Separators)

Die Formel überprüft, ob der Bereich zu den Trennzeichen mehr als eine Spalte ausweist. Ist dies der Fall, wird die erste Zeile des Bereichs verwendet und transponiert, sodass eine vertikale Liste entsteht. Ansonsten nehmen wir den übergebenen Wert.

Es könnte vorkommen, dass, aus welchen Gründen auch immer, ein Trennzeichen mehrfach in der Liste der Trennzeichen vorkommt oder die Trennzeichenliste leere Werte enthält. Das sollte ausgeschlossen werden, weshalb wir die Funktionen EINDEUTIG und FILTER verwenden, um S nochmals zu filtern. Es ergibt sich für T:

T=EINDEUTIG(FILTER(S;LÄNGE(S)>0))

Nun zu der Variable A. Die Idee ist, den Text in ein Array zu verwandeln, die Positionen der Trennzeichen im generierten Array zu vergleichen und daraus ein Positionsarray zu erstellen, womit dann die Teiltexte extrahiert werden können. Um einen Text in ein Array zu verwandeln, habe ich bereits die benutzerdefinierte LAMBDA-Funktion ARRAY.FROMTEXT erstellt. Die Formel für die Variable A sieht wie folgt aus:

A=ARRAY.FROMTEXT(INDEX(T;1;1)&Value&INDEX(T;1;1))

Wie zu sehen, wurde noch das erste Trennzeichen am Anfang und am Ende des aufzuteilenden Textes hinzugefügt. Das dient dazu, die Ober- und Untergrenzen für das später erstellte Positionsarray zu ermitteln. Das Ergebnis sieht dann wie in folgender Abbildung gezeigt aus.

Die Variablen R und C sollen als Laufvariablen dienen, mit denen eine Matrix von R Zeilen und C Spalten durchalufen werden kann. Dazu verwenden wir die Funktion SEQUENZ, welche die gewünschten Zahlenreihen erstellt:

R=SEQUENZ(ZEILEN(A))
C=SEQUENZ(1;ZEILEN(T))

Für R ergibt sich somit, bezogen auf das Beispiel, die Zahlenreihe 1 bis 12. Für C ergibt sich die Zahlenreihe 1 bis 2, da 2 Trennzeichen vorhanden sind.

Nun generieren wir eine Matrix, bei der ein Vergleich pro Trennzeichen angestellt wird. Die Formel dazu sieht wie folgt aus:

M=WENN(A=INDEX(T;C;1);R;0)

Das erste Trennzeichen – hier das @-Zeichen – wird mit jedem Element des zuvor erzeugten Textarrays in der Variable A verglichen. Wird das Zeichen gefunden, wird der Wert der Laufvariable R verwendet, ansonsten eine Null. Im zweiten Durchlauf, nämlich für die zweite Spalte, wird das zweite Trennzeichen – hier ein Punkt – verglichen. Es ergibt sich folgendes Bild:

Um das Positionsarray zu generieren müssten die zwei Spalten zusammengeführt werden. Das heißt der Wert 10 in C11 müsste in B11 wandern und die zweite Spalte verschwinden. Das kann durch eine Multiplikation von Matrizen erreicht werden. Hierzu stellt Excel die Funktion MMULT zur Verfügung. Die Formel für P lautet dann:

P=MMULT(M;SEQUENZ(SPALTEN(M);1;1;0))

Die Sequenz, die in der Formel für P erzeugt wird, beinhaltet genauso viele Zeilen wie M an Spalten hat, zählt aber nicht hoch. In diesem Fall wäre das die Zahlenreihe {1;1}. Wäre es z.B. 3 Trennzeichen, dann wäre die Zahlenreihe {1;1;1}, da M dann auch drei Spalten hätte. Nun brauchen wir nur noch die Nullen wegzufiltern, was in der Variable F passiert:

F=FILTER(P;P<>0)

Das Ergebnis sieht jetzt wir folgt abgebildet aus. Wir haben nun ein Array mit den einzelnen Position zu allen Trennzeichen. Wir erinnern uns, die Trennzeichen wurde dem Originaltext am Anfang und Ende hinzugefügt. Daher ist z.B. das @-Zeichen an Position 6 zu finden und nicht wie im Text an Position 5.

Was noch fehlt, ist die Länge der einzelnen Teiltexte, damit diese später anhand der Funktion TEIL extrahiert werden können. Das erledigen wir in der Variable L:

F=INDEX(F;SEQUENZ(ZEILEN(F)-1;1;2))-INDEX(F;SEQUENZ(ZEILEN(F)-1;1;1))-1

Dazu ducrhlaufen wir das Array F ab der zweiten Position und ziehen jeweils der Wert der vorherigen Position ab sowie zusätzlich abzüglich 1. Bezogen auf unser Beispiel erhalten wir dann die Zahlenreihe {4;3;2}.

Das war’s dann auch schon. Wir müssen nur noch die Teiltexte anhand der Werte von F und L extrahieren. Da wir die Länge L kennen, brauchen wir auch nur F bis zum vorletzten Elemente zu berücksichtigen. Schließlich ist das Ergebnis nochmal zu transponieren, damit ein horizontales dynamisches Array entsteht.

MTRANS(TEIL(Value;INDEX(F;SEQUENZ(ZEILEN(F)-1);0);L))

Damit ist nun die LET-Formel vollständig:

=LET(Value;A2;
     Separators;{"@";"."};
     S;WENN(SPALTEN(Separators)>1;
       MTRANS(INDEX(Separators;1;0));Separators);
     T;EINDEUTIG(FILTER(S;LÄNGE(S)>0));
     A;ARRAY.FROMTEXT(INDEX(T;1;1)&Value&INDEX(T;1;1));
     R;SEQUENZ(ZEILEN(A));
     C;SEQUENZ(1;ZEILEN(T));
     M;WENN(A=INDEX(T;C;1);R;0);
     P;MMULT(M;SEQUENZ(SPALTEN(M);1;1;0));
     F;FILTER(P;P<>0);
     L;INDEX(F;SEQUENZ(ZEILEN(F)-1;1;2))-
       INDEX(F;SEQUENZ(ZEILEN(F)-1;1;1))-1;
       MTRANS(TEIL(Value;INDEX(F;SEQUENZ(ZEILEN(F)-1);0);L)))

Und wir können nun unsere LAMBDA-Funktion definieren sowie im Namensmanager als TEXT.SPLIT ablegen:

=LAMBDA(Value;Separators;
 LET(S;WENN(SPALTEN(Separators)>1;
       MTRANS(INDEX(Separators;1;0));Separators);
     T;EINDEUTIG(FILTER(S;LÄNGE(S)>0));
     A;ARRAY.FROMTEXT(INDEX(T;1;1)&Value&INDEX(T;1;1));
     R;SEQUENZ(ZEILEN(A));
     C;SEQUENZ(1;ZEILEN(T));
     M;WENN(A=INDEX(T;C;1);R;0);
     P;MMULT(M;SEQUENZ(SPALTEN(M);1;1;0));
     F;FILTER(P;P<>0);
     L;INDEX(F;SEQUENZ(ZEILEN(F)-1;1;2))-
       INDEX(F;SEQUENZ(ZEILEN(F)-1;1;1))-1;
       MTRANS(TEIL(Value;INDEX(F;SEQUENZ(ZEILEN(F)-1);0);L))))

Jemand hatte mich letztens noch angesprochen und gemeint, dass die Punkte in der Benennung meiner Funktionen vielleicht nicht ganz optimal sind und möglicherweise Microsoft in zukünftigen Versionen keine Punkte in den Namen von LAMBDA-Funktionen erlauben würde, so wie es z.B. der Fall bei VBA-Funktionen ist. Die Punkte finde ich nun persönlich z.B. zum Gliedern der Funktionen ganz praktisch. Da aber die Namen zu den benutzerdefinierten LAMBDA-Funktionen vom User vergeben werden, ist eine Umbenennung ja möglich, sollten irgendwann mal keine Punkte mehr erlaubt sein.

Fazit

TEXT.SPLIT ist meine erste Funktion, die eine weitere LAMBDA-Funktion verwendet. Es werden sicherlich noch weitere folgen, aber soweit bin ich noch nicht. Und wie immer, ich freue mich über Verbesserungs- und Optimierungsvorschläge.

Happy Exceling :-)