Als je lijstje met directory-en-bestandsnamen zoals c:\map\submap\subsubmap\bestandsnaam.ext hebt, dan wordt al vlug de vraag gesteld of de bestandsnaam er ook eenvoudig van afgehaald kan worden. Het is mij in de afgelopen jaren al vaker gevraagd. En ik weet dat er een formule voor is (ok, het kan ook via vba) en die zoek ik dan op internet op. Maar hoe het werkt heb ik nooit begrepen. Daarom hieronder maar eens uitgelegd hoe e.e.a. werkt.
Als je in A1 de directory-en-bestand-tekst zet dan kun je met onderstaande formule in B1 de bestandsnaam met extensie berekenen: "c:\map\submap\subsubmap\bestandsnaam.ext"in cel A1 geeft in cel B1 als resultaat "bestandsnaam.ext" met deze formule:
=DEEL(A1;VIND.ALLES("*";SUBSTITUEREN(A1;"\";"*";LENGTE(A1)-LENGTE(SUBSTITUEREN(A1;"\";""))))+1;LENGTE(A1))
Deze formule bevat verschillende functies die de meeste Excel-gebruikers niet dagelijks zullen gebruiken zoals VIND.ALLES en SUBSTITUEREN. Daarom eerst een uitleg per functie.
=LENGTE(tekst of celverwijzing)
Lengte berekent het aantal tekens van een tekst in een cel.
De formule =LENGTE("abcde") of =LENGTE(A1) geeft als in cel A1 de tekst "abcde" staat als resultaat 5.
=SUBSTITUEREN(tekst, oude_tekst, nieuwe_tekst, rang_getal)
Deze functie vervangt in de tekst (opgegeven in een cel of ingevoerd in de functie) de oude tekst (of een enkel teken) door de nieuwe tekst vanaf een bepaalde positie)
De formule =SUBSTITUEREN("aababa";"b";"c";2) vervangt de tweede b en toont als antwoord "aabaca".
Met een combinatie van beide functies kan bepaald worden hoe vaak een teken in een tekst voor komt. Als je SUBSTITUEREN gebruikt om een teken te vervangen door niets, dan wordt de tekst korter en het verschil geeft dan aan hoe vaak een letter voor komt in de tekst.
De formule =LENGTE("aabaabaa")-LENGTE(SUBSTITUEREN("aabaabaa""b";"")) geeft 2 als resultaat want 2x wordt een "b" vervangen door niets en dus wordt de tekst 2 tekens korter.
De formule =LENGTE("c:\map\submap\subsubmap\bestand.ext")-LENGTE(SUBSTITUEREN("c:\map\submap\subsubmap\bestand.ext""\";"")) geeft 4 als resultaat.
Omdat in de functie SUBSTITUEREN het laatste argument een ranggetal is, wordt in =SUBSTITUEREN("c:\map\submap\subsubmap\bestand.ext";"\";"*";4) de 4e backslash vervangen door een "*" en geeft dit als resultaat "c:\map\submap\subsubmap*bestand.ext"".
=VIND.ALLES(zoeken_tekst;in_tekst;[begingetal])
Deze functie geeft aan op welke positie de "zoeken_tekst" voor komt in de "in_tekst". Optioneel kan worden aangeven vanaf welke positie in de tekst gezocht moet worden.
De formule =VIND.ALLES("b";"aabaabaa";1) geeft 3 als resultaat omdat vanaf het eerste teken gerekende de eerste "b" gevonden wordt op positie 3.
De formule =VIND.ALLES("b";"aabaabaa";4) geeft 6 als resultaat omdat vanaf het vierde teken gerekende de eerste "b" gevonden wordt op positie 6.
We kunnen bovenstaande functies combineren tot =VIND.ALLES("*";SUBSTITUEREN("c:\map\submap\subsubmap\bestand.ext";"\";"*";LENGTE("c:\map\submap\subsubmap\bestand.ext")-LENGTE(SUBSTITUEREN("c:\map\submap\subsubmap\bestand.ext";"\";""))))
Deze formule geeft als resultaat de 20e positie in de tekst waar de "*" wordt gevonden.
Om het begin van de bestandsnaam te vinden moet dit getal met 1 worden verhoogd.
=DEEL(tekst;begin_getal;aantal_tekens)
Deze functie haalt een gedeelte uit een tekst waarbij aangegeven waar dat gedeelte moet beginnen en hoe lang het moet zijn.
De functie =DEEL("abcdefg";3;2) geeft "cd" als resultaat want dit begint op de 3e positie en is 2 tekens lang. Als het aantal_tekens groter is dan dat er beschikbaar is dan worden alle beschikbare tekens getoond. De functie =DEEL("abcdefg";5;4) geeft "efg" als resultaat omdat vanaf de 5e positie maar 3 tekens meer beschikbaar zijn.
De combinatie
Door alles te combineren wordt de laatste backslash in de tekst gevonden en vanaf 1 positie verder wordt de tekst als resultaat gegeven en zo kan de naam van het bestand gescheiden worden van de directory.
En wil je ook nog de directory apart hebben, dan kun je met de functie =LINKS(tekst, aantal_tekens) dit doen. Het aantal tekens is dan het verschil tussen de lengte van de volledige directory minus de lengte van de bestandsnaam met extensie.
De oorspronkelijke formule die hier is gebruikt heb ik hier gevonden.
En is iets niet duidelijk, stuur dan een berichtje via het contactformulier van mijn website.
