(C) 2009 - 2021 by Mourad Louha · Alle Rechte vorbehalten

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.

TEXT.SPLIT

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.

TEXT.SPLIT - Beispiel

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.

TEXT.SPLIT - Text als Array

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:

TEXT.SPLIT - Ausgabematrix zum Vergleich

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.

TEXT.SPLIT - Gefiltere Ausgabematrix zum Vergleich

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 :-)

 

 
Comments

Hallo,
Eine sehr hilfreiche Funktion die ich sehr gebrauchen könnte da die neue entsprechende Funktion? von Microsoft Excel noch nicht für alle User frei gegeben ist. Leider kommt bei mir als Resultat immer #WERT. Ich habe die Lambda Funktion 1:1 im Namen Manager eingetragen und auch den Namen 1:1. die benötigte Funktion ARRAY.FROMTEXT existiert im Workbook. Alle anderen Array Funktionen die ich ausprobiert habe funktionieren einwandfrei. An was könnte es liegen?
Besten Dank

Mourad Louha

Hallo Wolfgang,

erstmal vielen Dank für’s Lob. Aus irgendeinem Grund hat sich in diesem Artikel wohl ein Fehler in der TEXT.SPLIT-Funktion eingeschlichen. Und zwar muss es bei ARRAY.FROMTEXT heißen:

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

Zuvor hatte ich noch Argumente drin, die da nicht hingehören. Ich habe es im Artikel ebenfalls korrigiert. Nun müsste es funktionieren.

Viele Grüße,
Mourad

Hallo Mourad,
Sorry dass ich schon wieder komme.
Besten Dank für die schnelle Antwort. Jetzt klappt die Funktion. Gehe ich richtig in der Annahme, dass immer nur eine Zelle ausgewählt werden kann und nicht ein Array und somit die Funktion (Formel) nach unten kopiert werden muss wenn mehrere Zellen aufgeteilt werden sollen? Bei mir klappt es jedenfalls nur immer mit einer Zelle, wähle ich ein Array aus wird nur die erste Zeile aufgeteilt.
Viele Grüsse
Wolfgang

Mourad Louha

Hallo Wolfgang,

ja, das ist richtig. Diese Version kann in der Tat nur eine Zelle aufsplitten. Und zwar deswegen, weil mehrere Trennzeichen verwendet werden dürfen und Excel nicht mit 3D-Arrays (Arrays in Array, Formel) umgehen kann. Mit den weiteren, neuen, Funktionen wie bsp. VSTACK kann man das ggf. umgehen.

Wenn Du aber nur ein Trennzeichen hast, um den Text aufzuteilen, könntest Du ARRAY.SPLIT versuchen. Diese Funktion nimmt auch einen Bereich (einspaltig) entgegen.

Gruß,
Mourad