Momentan untersuche ich die Registry Zugriffe von Excel & Co. und habe mir zu diesem Zweck eine Excel Tabelle angelegt, wo einzelne Registry Pfade in Schlüssel und Werte zerlegt werden. Dabei kam mir dann die Idee, dies zur Zerlegung einen vollständigen Pfads zu einer Datei in Pfadname und Dateiname per Excel Formel zu zerlegen und in diesem Artikel zu beschreiben. Eine Beispielmappe kann am Ende des Artikels heruntergeladen werden.
Ein Pfad zu einer Datei setzt sich in der Regel aus einer Laufwerksangabe, einer Folge von Ordnernamen, getrennt durch ein Trennzeichen und einem Dateinamen zusammen. Für Windows Betriebssystem ist das Trennzeichen der „Backslash“, also „\“.
Je nachdem, wo sich die Datei befindet, ist der Pfad mal kürzer, mal länger. Um den Dateinamen in Excel zu ermitteln, müssen wir also wissen, an welcher Stelle sich das letzte Trennzeichen befindet. Dann könnten wir die Excel Formel „Rechts()“ verwenden und den Dateinamen extrahieren.
Die Anzahl der Vorkommen einer Zeichenkette in einem Text lässt sich in Excel relativ einfach ermitteln, dazu brauchen wir nur im durchsuchten Text die gesuchte Zeichenkette durch eine leere zu ersetzen und die Differenz der Originallänge zur verbliebenen Länge zu berechnen. Teilen wir anschließend das Ergebnis durch die Länge der gesuchten Zeichenkette, erhalten wir die Anzahl der Vorkommen. Hierzu hatte ich auch den Artikel „Anzahl eines bestimmten Wortes in einer Zelle ermitteln“ publiziert. Bei einem Trennzeichen der Länge 1, ergibt sich dann beispielsweise:
D7 = LÄNGE($D$3)-LÄNGE(WECHSELN($D$3;$D$4;""))
Wobei sich hier das Trennzeichen in D4 befindet und der Dateiname in D3 abgelegt ist, in dem oben abgebildeten Beispiel: „C:\Users\Mourad\Dokumente\Testdatei.xls“.
Als Ergebnis erhalten wir „4“, dies entspricht genau der Anzahl an „\“ im Text. Die Excel Formel „Wechseln()“ erlaubt es nun, eine Zeichenkette in einem Text durch eine andere zu ersetzen. Als letztes Argument kann in dieser Formel angegeben werden, welches Vorkommen im Text ersetzt werden soll. Wenn wir bei unserem Beispiel bleiben, wäre das dann, zunächst statisch:
D9 = WECHSELN("C:\Users\Mourad\Dokumente\Testdatei.xls"; "\"; "$$$"; 4)
Nach dem Ersetzen der statischen Texte durch die Bezüge erhalten wir:
D9 = WECHSELN($D$3;$D$4;$D$5;$D$7)
Wie in der Abbildung zu sehen, enthält die Zelle D9 nun den Wert „C:\Users\Mourad\Dokumente$$$Testdatei.xls“, wobei wir statt „$$$“ auch etwas anderes hätten verwenden können. Der verwendete Text darf aber nicht schon mal im Pfad vorkommen.
Jetzt ist es relativ einfach, den Text in Pfadname und Dateiname aufzuteilen. Die Formel „Finden()“ liefert die Position eines gesuchten Textes – hier dann „$$$“ – und über die Formeln „Links()“ und „Rechts()“ können wir die gesuchten Texte extrahieren. Ergebnisse:
D11 = LINKS($D$9;FINDEN($D$5;$D$9)-1)
D13 = RECHTS($D$9;LÄNGE($D$9)-LÄNGE($D$11)-LÄNGE($D$5))
Die erste Variante zur Ermittlung des Dateinamens stützt sich darauf, dass der Pfad bereits extrahiert wurde. Möchten wir dies jedoch unabhängig vom Pfad tun, können wir diese Formel verwenden:
D13 = RECHTS($D$9;LÄNGE($D$9)-FINDEN($D$5;$D$9)-LÄNGE($D$5)+1)
Gesamtformel ermitteln
Wenn man nun zur Ermittlung einer Gesamtformel eine einfache Substitution innerhalb und mit den Formeln der Hilfszellen durchführt, erhält man die recht lange Formel zu Ermittlung des Pfades:
D18 = LINKS(WECHSELN($D$3;$D$4;$D$5;LÄNGE($D$3)-
LÄNGE( WECHSELN($D$3;$D$4;"")));FINDEN($D$5;
WECHSELN($D$3;$D$4;$D$5;LÄNGE($D$3)-
LÄNGE(WECHSELN($D$3;$D$4;""))))-1)
Im ersten Argument von „Links()“ wird hier das Ergebnis nach dem Austausch des letzten „\“ durch „$$$“ durchsucht. Da aber nun das letzte „\“ ausgetauscht wurde, ändert sich nichts an der Anzahl der Zeichen vor dem letzten „\“ bzw. dann „$$$“. Somit können wir direkt den Originalpfad durchsuchen und im ersten Argument „WECHSELN($D$3;$D$4; $D$5;LÄNGE($D$3)-LÄNGE(WECHSELN($D$3;$D$4;““)))“ einfach durch den Text selbst ersetzen, also D3. Wir erhalten somit als Endversion:
D19 = LINKS($D$3;FINDEN($D$5;
WECHSELN($D$3;$D$4;$D$5;LÄNGE($D$3)-
LÄNGE(WECHSELN($D$3;$D$4;""))))-1)
Für den Dateinamen erhalten wir nach der Substitution die ebenfalls komplexere Formel:
D21 = RECHTS(WECHSELN($D$3;$D$4;$D$5;
LÄNGE($D$3)-LÄNGE( WECHSELN($D$3;$D$4;"")));
LÄNGE(WECHSELN($D$3;$D$4;$D$5;LÄNGE($D$3)-
LÄNGE( WECHSELN($D$3;$D$4;""))))-FINDEN($D$5;
WECHSELN($D$3;$D$4;$D$5; LÄNGE($D$3)-
LÄNGE(WECHSELN($D$3;$D$4;""))))-LÄNGE($D$5)+1)
Auch hier lässt sich diese Formel optimieren. Wie für die Pfadangabe, brauchen wir im ersten Argument keinen Austausch des letzten „\“ zum Speicherort der Datei vorzunehmen und erhalten zunächst:
D22 = RECHTS($D$3;LÄNGE($D$3)-
FINDEN($D$5;WECHSELN($D$3;$D$4;$D$5;
LÄNGE($D$3)-LÄNGE(WECHSELN($D$3;$D$4;""))))-LÄNGE($D$5)+1)
Wir dürfen hier jedoch nicht die Länge unserer Austauschzeichenkette subtrahieren, da wir ja als Ausgangstext den Originaltext zum Speicherort verwenden. Somit ergibt sich als Endergebnis:
D22 = RECHTS($D$3;LÄNGE($D$3)-
FINDEN($D$5;WECHSELN($D$3;$D$4;$D$5;
LÄNGE($D$3)-LÄNGE(WECHSELN($D$3;$D$4;"")))))
An dieser Stelle übrigens noch ein Danke schön an Andreas für ein paar Optimierungstipps. Es ist sicherlich noch möglich, die Formeln so zu erweitern, dass zum Beispiel abgefragt würde, ob „Finden()“ einen Fehler liefert oder der Austauschtext „$$$“ im Dateinamen enthalten ist.
VBA Code verwenden
Das Ermitteln des Pfades und Dateienamens kann natürlich auch in VBA gemacht werden. Folgend ein Beispielcode mit zwei Funktionen „GetFilename()“ und „GetPath()“ zum Ermitteln des Dateinamens und des Pfades:
Update am 21.09.2011: ich habe eben gemerkt, dass der Wechsel des Layouts Auswirkungen auf einige Tags in diesem Artikel hatte. Und zwar wurde die Formeln nicht in mehreren Zeilen, sondern nur in einer Zeile angezeigt und am Ende abgeschnitten. Habe dies nun behoben und manuelle Umbrüche eingefügt.
Dieser Artikel wurde auch in der englischen Verson vom Excel Ticker publiziert.
