Ik importeerde laatst een gegevensset in Excel. De set bevatte voor- en achternamen in een enkele kolom. Niet zo handig als je wilt sorteren op achternaam. Ik wilde de achternamen in een losse kolom hebben, maar dat bleek niet zo eenvoudig. Hieronder een paar tips om toch tot het gewenste resultaat te komen.
Tekst naar kolommen
Een eenvoudige manier om gegevens in kolommen te krijgen in Tekst naar kolommen (onder het tabblad gegevens). Als je wel eens een .csv-bestand hebt geïmporteerd (bijvoorbeeld uit Google Analytics), dan heb je hier waarschijnlijk al wel eens gebruik van gemaakt. Door als scheidingsteken te kiezen voor spatie kun je de tekstuele inhoud van een cel zo spreiden over kolommen.
Je begint bijvoorbeeld zo:
volledige naam in 1 kolom
Na het splitsen krijg je dan:
tekst in kolommen
Je ziet het: de voornamen staan keurig in de eerste kolom, maar de achternamen staan verspreid over 3 kolommen. Lang leve de tussenvoegsels. Sorteren op achternaam wordt zo moeilijk.
Achternamen uit een Excel-cel halen met een functie
Gelukkig bevat Excel allerlei opties om de inhoud van een cel op te delen. Het is alleen jammer, dat die opties moeilijk te doorgronden zijn als je niet veel ervaring hebt met formules en functies. Na wat speurwerk (en het doen van wat kleine aanpassingen om de boel in Excel 2007 werkend te krijgen) heb ik 3 functies gevonden die doen wat ik zocht. Achtereenvolgens om:
- de voornaam weer te geven
- de achternaam weer te geven
- de voornaam en tussenvoegsels weer te geven
Toegegeven: bij complexe (dubbele) namen, kan het zijn dat ook deze methode niet foutloos werkt. Maar het is in de meeste gevallen voldoende. Het voorbeeldbestand komt er dan zo uit te zien:
Ik heb in kolom A de oorspronkelijke namen geplakt.
In cel B2 heb ik deze formule geplakt:
=LINKS(A2; VIND.ALLES(" ";A2)-1)
Voor Excel 2010:
=LINKS(A2; VINDEN.ALLES(" ";A2)-1)
In cel C2 staat:
=RECHTS(A2;LENGTE(A2)-VIND.ALLES("*";SUBSTITUEREN(A2;" ";"*";LENGTE(A2)-(LENGTE(SUBSTITUEREN(A2;" ";""))))))
Voor Excel 2010:
=RECHTS(A2;LENGTE(A2)-VINDEN.ALLES("*";SUBSTITUEREN(A2;" ";"*";LENGTE(A2)-(LENGTE(SUBSTITUEREN(A2;" ";""))))))
In cel D2 staat:
=DEEL(A2;1;LENGTE(A2)-LENGTE(RECHTS(A2;LENGTE(A2)-VIND.ALLES("#";SUBSTITUEREN(A2;" ";"#";LENGTE(A2)-LENGTE(SUBSTITUEREN(A2;" ";""))))))-1)
Voor Excel 2010:
=MIDDEN(A2;1;LENGTE(A2)-LENGTE(RECHTS(A2;LENGTE(A2)-VINDEN.ALLES("#";SUBSTITUEREN(A2;" ";"#";LENGTE(A2)-LENGTE(SUBSTITUEREN(A2;" ";""))))))-1)
Je kunt het voorbeeldbestand downloaden om de formules in werking te zien. Ik maakte het voorbeeld in Excel 2007. Ik geloof dat de formules het ook moeten doen in Excel 2010 en hoger, maar het zou kunnen dat je dan VINDEN.ALLES moet typen in plaats van VIND.ALLES. En ik meen ook dat DEEL in 2007 is omgevormd tot MIDDEN in 2010.
Afhankelijk van de cel waarbij je de waarden zoekt kun je de verwijzing naar die cel (in bovenstaande is dat A2) vervangen door de cel die de brongegevens bevat. Je kunt die formules vervolgens naar de rest van de kolom kopiëren door met je muis te dubbelklikken op de rechteronderhoek van de cel waarin je de formule hebt gezet.
Het dikke witte kruisje van je mousepointer verandert in een smaller zwart kruisje als je op het blokje rechtsonder in de cel mikt:
Als je liever de controle houdt, kun je dat zwarte blokje ook naar beneden slepen in plaats van erop te dubbelklikken.
Concluderend: het lukt dus wel met wat moeite. Maar als je zelf de baas bent over het bronbestand: zorg dat de verschillende delen van een naam als aparte velden worden opgeslagen, zodat je geen trucs hoeft uit te halen om de juiste sortering te kunnen maken.
Update: Flash Fill
Als je Excel 2013 of nieuwe gebruikt, dan is er nóg een optie. Met Flash Fill vult Excel de juiste gegevens in in een kolom. Werkt erg handig. Hier leg ik uit hoe je Flash Fill gebruikt.
