Dateinamen über dynamische Arrays ermitteln

Meine Auswertungen der Zugriffe auf diesen Blog führen in den Zugriffsstatistiken öfter mal Abrufe von deutlich älteren Blogposts auf. Einer dieser Blogposts beschreibt, wie aus einem vollständigen Pfad zu einer Datei der Dateiname per Formel ermittelt werden kann. Ab und zu schaue ich auch mal in die alten Artikel rein, so wie heute in den zuvor genannten Artikel, der nun schon über 10 Jahre alt ist. Und dabei hatte ich mich dann gefragt, ob es möglich wäre, das Problem auch mit einer Formel zu lösen, die auf dynamischen Arrays basiert.

Die Antwort ist, ja, das geht. Meine Lösung möchte ich nun niemanden vorenthalten und nachfolgend vorstellen. Als Nebeneffekt kommt dann noch zusätzlich eine Formel heraus, um eine Zeichenkette zu invertieren.

Ermittlung des Dateinamens ohne dynamische Arrays

Zunächst jedoch eine kurze Zusammenfassung des Artikels von vor 10 Jahren. Die Formel hat immer noch ihre Gültigkeit und kann selbstverständlich auch in neueren Excel-Versionen verwendet werden.

Die bei dieser Methode verwendete Formel lautet:

=RECHTS($B$2;LÄNGE($B$2)-
 FINDEN("$";WECHSELN($B$2;"\";"$";
 LÄNGE($B$2)-LÄNGE(WECHSELN($B$2;"\";"")))))

Was passiert hier genau? Lesen wir die Formel rückwärts, wird zunächst die Länge des Texts in Zelle B2, also der Angabe des Dateinamens inklusive dem Pfad, ermittelt. In dem Beispiel beinhaltet der Text 29 Zeichen. Anschließend wird das Pfad-Trennzeichen anhand der Funktion WECHSELN durch nichts ersetzt und wiederum aus dem Ergebnis die Anzahl der Zeichen ermittelt. Das sind hier 26 Zeichen, da der Backslash insgesamt dreimal vorkommt. Dies entspricht dem Teil LÄNGE($B$2)-LÄNGE(WECHSELN($B$2;"\";"")) in der Formel.

Die Funktion WECHSELN kennt ein optionales Argument, um einen Text an einer bestimmten Position auszutauschen. Das passiert, von rechts aus gesehen, bei der zweiten Verwendung von WECHSELN in der Formel. Dort wird das letzte Backslash-Zeichen durch ein Dollar-Zeichen ersetzt. Zu diesem Zeitpunkt würde der Pfad lauten: D:\Excel\Blog$Dateinamen.xlsx

Nun kann die Funktion FINDEN angewendet werden, die die Position eines Zeichens ermittelt. Gesucht wird die Position des Dollarzeichens. In unserem Fall ergibt das die Position 14. Da sich der Dateiname sich am rechten Ende des Pfades befindet, verwenden wir die Funktion RECHTS und lassen uns X Zeichen zurückgeben. X entspricht hier der Gesamtanzahl der Zeichen des Dateinamens inklusive des Pfades abzüglich der gefundenen Position des Dollarzeichens – also 29 minus 14 gleich 15. Das war’s dann auch schon.

Ermittlung des Dateinamens mit dynamischen Arrays

Würde man das Ermitteln das Dateinamens in VBA bewerkstelligen, wäre eine mögliche Methode, den vollständigen Dateinamen zuerst zu invertieren, dann nach dem Pfad-Trennzeichen zu splitten und schließlich das erste Element des erzeugten Arrays wieder zu invertieren.

Eine moderne Excel-Version kennt dynamische Arrays sowie einige neue Funktionen. Eine dieser Funktionen ist SEQUENZ, die dynamisch eine Zahlenfolge als Array liefert. Meistens liefern die gewöhnlichen Funktionen, sofern Sie Arrays in Ihren Argumente beinhalten, ebenfalls ein dynamisches Array zurück.

Schauen wir mal, was denn passiert, wenn wir auf die Angabe zum Pfad und zum Dateinamen folgende Formel anwenden:

=RECHTS($B$2;SEQUENZ(LÄNGE($B$2)))

Wie in nachfolgender Abbildung zu sehen, erzeugt die Formel eine Liste von Teiltexten, die von rechts anfangen. Da SEQUENZ eine aufsteigende Zahlenfolge zurückliefert – nämlich von 1 bis zur Länge von B2 – enthalten die einzelnen Elemente des Arrays die jeweiligen Ergebnisse der Funktion RECHTS. Beispielsweise ist dies an Position 4 dann xlsx.

Das hilft aber noch nicht so richtig weiter. Ergänzen wir die Formel um die Funktion LINKS und extrahieren wir immer das erste Zeichen. Das ergibt:

=LINKS(RECHTS($B$2;SEQUENZ(LÄNGE($B$2)));1))

Nun erhalten wir eine Liste – also ein Array – welches pro Element jeweils einen Buchstaben des vollständigen Dateinamens enthält und sich rückwärts lesen lässt.

Die obere Abbildung zeigt nur einen Auschnitt der Elemente des Arrays. Insgesamt enthält das Array 29 Elemente. Das letzte Element enthält dann den Laufwerksbuchstaben D.

Da das Array jetzt nur einen einzelnen Buchstaben pro Element enthält, lässt sich dieses auch nach dem ersten Vorkommen des Pfad-Trennzeichens durchsuchen. Hier kommt die Funktion VERGLEICH zum Einsatz. Wir erhalten somit:

=VERGLEICH("\";LINKS(RECHTS($B$2;SEQUENZ(LÄNGE($B$2)));1);0)

Die Funktion liefert den Wert 16, was genau der Position des letzten Backslashs in der Angabe zum Dateinamen inklusive Pfad entspricht, wenn von rechts gezählt wird.

Da nun die Position vorliegt, ist es ein leichtes den Dateinamen zu extrahieren. Wir wenden die Funktion RECHTS nochmals an und berücksichtigen, dass wir 1 von der Position abziehen müssen, damit das Pfadtrennzeichen nicht im Ergebnis erscheint. Die Formel lautet im Endergebnis nun:

=RECHTS($B$2;VERGLEICH("\";
 LINKS(RECHTS($B$2;SEQUENZ(LÄNGE($B$2)));1);0)-1)

Text invertieren als Nebeneffekt

Ein Nebeneffekt der Formel ist, dass sich in Excel eine Zeichenkette ziemlich einfach invertieren lässt. Wenn wir statt der Funktion VERGLEICH die Funktion TEXTKETTE verwenden, erhalten wir:

=TEXTKETTE(LINKS(RECHTS($B$2;SEQUENZ(LÄNGE($B$2)));1))

Das Ergebnis wäre dann in dem Beispiel xslx.nemanietaD\golB\lecxE\:D.

Fazit

Wie so oft in Excel, führen viele Wege nach Rom. Die hier vorgestellte Formel ist sicherlich nur eine der Varianten, um aus einem vollständigen Dateinamen den Namen der Datei zu extrahieren. Zudem ist eine Bewertung, ob nun die Methode mit oder ohne dynamisches Array besser oder schlechter ist, hier sicherlich nicht sinnvoll. Ich denke, das möge jede/r für sich selbst entscheiden.

Mit einigen Anpassungen würde die Formel zu den dynamischen Arrays übrigens auch in einer älteren Excel-Version ohne dynamische Arrays funktionieren. Da in älteren Excel-Versionen die Funktion SEQUENZ nicht vorhanden ist, müsste diese beispielsweise durch SPALTE($1:$1) ersetzt werden und die Formel als Matrixformel eingegeben werden. Die Formel zum Invertieren der Zeichenfolge hätte allerdings kein Pendant in einer älteren Excel-Version, die nicht TEXTKETTE kennt.

Happy Exceling :-)