Ende Oktober 2020 hatte ich in dem Artikel Dateinamen über dynamische Arrays ermitteln eine Methode vorgestellt, wie der Dateiname aus einer vollständigen Pfadangabe zu einer Datei anhand dynamischer Arrays extrahiert werden kann. Die Idee bestand darin, aus der vollständigen Pfadangabe ein Array zu erstellen, welches den Text in umgekehrter Reihenfolge enthält, dieses dann ab dem ersten Backslash abzuschneiden und wieder zusammenzusetzen. Die LET-Funktion zum Erstellen des Array sieht in dem besagten Artikel wie folgt aus:
=LET(Value;A1;LINKS(RECHTS(Value;SEQUENZ(LÄNGE(Value)));1))
In einem weiteren Artikel hatte ich mit dem Erscheinen der LAMBDA-Funktion in Excel Anfang Dezember 2020 meine benutzerdefinierte Funktion ARRAY.REVERSE vorgestellt, die generell ein Array invertiert. Zukünftig möchte ich mir jedoch eine Bibliothek an allgemein verwendbaren Funktionen aufbauen, weshalb dann eine Funktion ARRAY.FROMTEXT sinnvoll wäre, die einen Text in ein Array umwandelt, ohne es zu invertieren. Somit lassen sich dann beide Funktionen kombinieren:
=ARRAY.REVERSE(ARRAY.FROMTEXT(A1))
Dieser Artikel stellt die Funktion ARRAY.FROMTEXT vor. Zudem habe ich noch die erweiterte Version ARRAY.FROMTEXT.EX implementiert, die mir einen Text in definierbare Blöcke aufteilt und hierbei einige Steuerungsparameter berücksichtigt.
ARRAY.FROMTEXT
Die Funktion ist letztlich ziemlich einfach zu implementieren. Zunächst die LET-Form, die wie folgt aussieht:
=LET(Value;A2;TEIL(Value;SEQUENZ(LÄNGE(Value));1))
Erwartet wird ein Text bzw. ein Bezug auf eine Zelle, die Text enthält. Die Funktion SEQUENZ erstellt eine Zahlenreihe von 1 bis zur Anzahl der Zeichen des Textes. Über die Funktion TEIL wird dann jedes einzelne Zeichen extrahiert.
Das war’s dann auch schon. Um eine benutzerdefinierte Funktion zu erstellen, kapseln wir die Formel in eine LAMBDA-Funktion, erstellen im Namensmanager den Namen ARRAY.FROMTEXT und weisen dem Namen die LAMBDA-Funktion zu:
=LAMBDA(Value;TEIL(Value;SEQUENZ(LÄNGE(Value));1))
ARRAY.FROMTEXT.EX
Die Funktion ARRAY.FROMTEXT.EX soll mir einen Text in ein Array zerlegen und hierbei Blöcke fester Breite bilden. Zudem soll es möglich sein, dass die Blöcke von rechts oder links starten und je nach Richtung optional mit einem Zeichen aufgefüllt werden können. Schließlich soll es möglich sein, dem Array eine vorgegebene Anzahl an Blöcken hinzuzufügen.
Eine Pseudoformel mit LET sieht somit wie folgt aus:
=LET(Value;C2;
Blocksize;C3;
Direction;C4;
FillCharacter;C5;
AdditionalRows;C6;
R;Hilfsvariable für die Anzahl der Zeilen;
F;Hilfsvariable für das Füllzeichen;
S;Hilfsvariable für den Text;
Berechnung vom Ergebnis)
Die Variablen Value, Blocksize, Direction, FillCharacter und AdditionalRows definieren respektive die Eingangswerte für den Text, die Anzahl der Zeichen pro Block, die Richtung, das Füllzeichen und die Anzahl zusätzlich zu generierender Zeilen.
Beispielsweise, bezogen auf die Werte der Variablen in Spalte C in der oberen Abbildung, wird der Text mit 6 Zeichen in zwei Zeilen aufgeteilt, die die Werte ABCD und EF enthalten. Es soll aber noch eine Zeile hinzugefügt werden. Gleichzeitig sind die zweite und dritte Zeile des Array mit dem angegebenen Zeichen aufzufüllen, bis die Blocklänge erreicht ist.
Schauen wir uns die Hilfsvariable R an. Diese soll anhand der Blocklänge die Anzahl der Zeilen ermitteln und gegebenenfalls die angegebene Anzahl an Zeilen hinzufügen. Die Formel für R sieht dann wie folgt aus:
R=AUFRUNDEN(LÄNGE(Value)/Blocksize;0)+
WENN(ISTZAHL(AdditionalRows);AdditionalRows;0)
Über Funktion AUFRUNDEN erhalten wir die Anzahl der Zeilen, die sich durch die Länge des Textes und der Blocklänge ergibt. Ist AdditionalRows angegeben und eine Zahl, fügen wir diese Zahl hinzu.
Nun zum Füllzeichen, wo die Formel wie folgt aussieht:
F=WIEDERHOLEN(WENN(ISTLEER(FillCharacter);ZEICHEN(1);
LINKS(FillCharacter;1));R*Blocksize-LÄNGE(Value))
Hier wird die Anzahl der Zeichen berechnet, die benötigt werden, um den übergebenen Text so mit dem Füllzeichen aufzufüllen, dass die Länge des Textes genau einem Vielfachen der Blocklänge entspricht. In dem oben abgebildeten Beispiel in Spalte B hat der Text 6 Zeichen. Es fehlen somit zwei Füllzeichen, um eine Länge von 8 = Blocklänge x Zeilen zu erreichen. Wurde kein Füllzeichen angegeben, verwenden wir das erste Zeichen im Zeichensatz, was später wieder in der Ausgabe entfernt wird.
Verbleibt die Variable S, die dem Text je nach angegebener Richtung den Wert der Variable F entweder links voranstellt oder rechts hinten anhängt. Wird keine Richtung angegeben – diese kann den Wert 1 für links und den Wert 2 für rechts annehmen – greift die Standardmethode, die dem Wert 1 entspricht.
S=WENN(ISTZAHL(Direction);WENNFEHLER(
WAHL(Direction;Value&F;F&Value);Value);Value&F)
Das Aufteilen des Textes gestaltet sich nun mit den Hilfsvariablen recht einfach:
=WECHSELN(TEIL(S;SEQUENZ(R;1;1;Blocksize);Blocksize);ZEICHEN(1);"")
Die Funktion SEQUENZ erstellt eine Zahlenreihe mit R Werten und verwendet als Schrittweite die Blockgröße. Bezogen auf das zuvor abgebildete Beispiel würde somit die Zahlenreihe {1;5;9; …} erzeugt werden. Die Funktion TEIL extrahiert dann entsprechend viele Zeichen aus dem Text S. Und, das Ergebnis wird um das gegebenenfalls erzeugte Zeichen 1 bereinigt.
Die vollständige LET-Formel sieht dann wie folgt aus:
=LET(Value;C2;
Blocksize;C3;
Direction;C4;
FillCharacter;C5;
AdditionalRows;C6;
R;AUFRUNDEN(LÄNGE(Value)/Blocksize;0)+
WENN(ISTZAHL(AdditionalRows);AdditionalRows;0);
F;WIEDERHOLEN(WENN(ISTLEER(FillCharacter);ZEICHEN(1);
LINKS(FillCharacter;1));R*Blocksize-LÄNGE(Value));
S;WENN(ISTZAHL(Direction);WENNFEHLER(
WAHL(Direction;Value&F;F&Value);Value);Value&F);
WECHSELN(TEIL(S;SEQUENZ(R;1;1;Blocksize);Blocksize);ZEICHEN(1);""))
Hieraus lässt sich dann wieder eine LAMBDA-Funktion erstellen:
=LAMBDA(Value;Blocksize;Direction;FillCharacter;AdditionalRows;
LET(R;AUFRUNDEN(LÄNGE(Value)/Blocksize;0)+
WENN(ISTZAHL(AdditionalRows);AdditionalRows;0);
F;WIEDERHOLEN(WENN(ISTLEER(FillCharacter);ZEICHEN(1);
LINKS(FillCharacter;1));R*Blocksize-LÄNGE(Value));
S;WENN(ISTZAHL(Direction);WENNFEHLER(
WAHL(Direction;Value&F;F&Value);Value);Value&F);
WECHSELN(TEIL(S;SEQUENZ(R;1;1;Blocksize);Blocksize);ZEICHEN(1);"")))
Die Funktion ARRAY.FROMTEXT.EX hat sicherlich noch etwas Verbesserungspotential, da z.B. keine Überprüfung der Eingangsparameter für Value und Blocksize stattfindet. Für den Zweck, den die Funktion später erfüllen soll – nämlich als Hilfsfunktion zum Addieren großer Zahlen in Excel – ist die aktuelle Variante jedoch erstmal ausreichend.
Fazit
Die beiden Funktionen ARRAY.FROMTEXT und ARRAY.FROMTEXT.EX ergänzen nun meine Bibliothek an Array-Funktionen, die bereits die Funktionen ARRAY.APPEND, ARRAY.CREATE, ARRAY.REMOVE.FIRSTN, ARRAY.REMOVE.LASTN und ARRAY.REVERSE enthält.
Happy Exceling :-)
