Voor deze blog ga ik er van uit dat je vertrouwd bent met de text-formules ‘left’, ‘right’ , mid’ en de techniek ‘Text-To-Columns’. Deze techniek wordt gebruikt om een tekst met scheidingstekens in aparte kolommen op te splitsen. Indien alle lijnen evenveel scheidingstekens bevatten kan dit een snelle oplossing vormen. Indien elke lijn een variërend aantal scheidingstekens bevat, zal de oplossing in een verschillend aantal kolommen resulteren. (zie de screenshot hieronder)
In de blog van vandaag gaan we uit van een ‘file-path’ structuur waarvan we telkens de naam van het bestand wensen te bekomen. Aangezien alle bestanden een aparte folderstructuur hebben, zou splitsen in kolommen onderstaand resultaat opleveren. De naam van het bestand wordt na het splitsen telkens in een andere kolom vermeld.
Op het einde van deze blog gaan we met de functie ‘Mid’ het laatste deel van de tekst, de bestandsnaam, afknippen. Je hebt het goed gelezen, niet met de functie Right !
Eerst trachten we te achterhalen welk het laatst gebruikte scheidingsteken is. Helaas gaat het hier telkens over hetzelfde scheidingsteken ‘\’ dat vaker in de tekst voorkomt. Als het laatste teken een uniek teken zou zijn, konden we dat teken gewoon zoeken via de functie ‘Find’.
Ik geef al even de oplossing mee in het geval dat het laatste teken gekend en uniek zou zijn. Nadien voeren we dan een moeilijkere, maar noodzakelijke, oplossing uit voor het bovenstaande voorbeeld.
We gaan van het volgende voorbeeld uit:
‘C:\Test\Main#File1.doc’
In dit voorbeeld staat een uniek teken, #. Hierdoor kunnen we de oplossing als volgt omschrijven:
=MID( de cel met het path ; find ( “#”; de cel met het path ) + 1 ; 30 )
Uitleg:
De Find functie zoekt naar het “#” in de tekst en zal als oplossing de positie geven van dat teken. Daar tellen we 1 waarde bij omdat we net een teken verder willen starten.
In de Mid functie kunnen we een lengte meegeven die groter is dan de lengte van de tekst zelf. Hierdoor vermijden we het gebruik van de functie ‘Right’ waardoor de oplossing iets complexer zou zijn.
Voor het figuur in het begin van deze blog geldt deze oplossing dus niet. De oplossing zelf is wel de juiste, maar we hebben in dit geval geen uniek teken.
We gaan Excel vragen om het laatste scheidingsteken te vervangen door een uniek teken. Dat uniek teken is dan makkelijk te herkennen als scheidingsteken voor onze tekst. Hiervoor gaan we gebruik maken van de SUBSTITUTE functie. Met deze functie kan je een teken in een tekst vervangen door een andere teken.
De functie ziet er als volgt uit:
Geef in het eerste argument mee in welke cel de tekst staat. Het tweede argument verwijst naar de tekst die je wil vervangen, door de tekst die vermeld wordt in het derde argument. Met het 4de argument geef je aan dat enkel het x-ste teken vervangen dient te worden. Op dit moment zijn we nog niet zeker van dit ‘instance-nummer’.
Om de laatste instantie van een teken te kennen, gebruiken we opnieuw de substitute-functie, weliswaar in een creatieve vorm :-).
Voor je van start gaat zorg je voor 3 vrije kolommen rechts van de kolom met de bestandslocatie.
In de eerste vrije kolom zet je de volgende berekening:
= len (tekst) – len ( substitute ( tekst ; “\” ; “”) )
Je berekent dus de lengte van de tekst, verminderd met de lengte van de tekst indien je alle schuine tekens zou vervangen door niets. Zo krijg je een getal dat aangeeft hoeveel ‘\’ tekens er gebruikt worden.
In de tweede kolom gebruik je de Substitute-functie om de ‘\’ te vervangen door een ‘#’-teken, maar enkel voor de instantie die overeenkomt met de vorige berekening.
Onze oplossing van eerder in de blog kan nu naar deze cel verwijzen om te zoeken naar het unieke ‘#’ teken. Een opmerkzame lezer zou als alternatief deze oplossing reeds met ’text-to-columns’ kunnen splitsen. Vergeet dan niet je formule eerst als waarde te plakken (Paste values).
De durvers onder jullie kunnen dit allemaal in 1 cel combineren:
Tip:
Plak het adres van je folder/map in FireFox of Chrome (niet in IE) en je krijgt een pagina met de inhoud van deze folder. Dit kan je kopiëren/plakken in Excel. Eventueel voeg je deze cellen samen met het folderadres.
Morgen: Dag 12: Datums omzetten in vreemde talen.
