Webcontent van a tot z
info@elroco.nl

Namen splitsen in Excel

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

volledige naam in 1 kolom

Na het splitsen krijg je dan:

tekst in kolommen

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:

formules in werking

 

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:

 

cel-met-rechterhoek-geselecteerd

 

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.

Summary
Namen splitsen in Excel
Article Name
Namen splitsen in Excel
Description
Je kunt namen splitsen in Excel door gebruik te maken van formules.
Author
elroco
  • Ik wil niet ofshowen hoor, maar…

    Met Alt-F11 naar de code, daar invoegen->nieuwe module en daarin:

    Function naamdeel(volledig As String, deel As Integer) As String
    'naamdeel: deel 1 is voornaam, 2 is tussendeel, 3 is achternaam
    Dim regEx As Object

    Set regEx = CreateObject("vbscript.regexp")

    regEx.Pattern = "^(\S+)\s+(?:(.*)\s+)?(\S+)"
    regEx.IgnoreCase = True
    regEx.Global = True

    Dim Matches As Object
    Set Matches = regEx.Execute(volledig)
    If Matches.Count = 0 Then
    naamdeel = ""
    Exit Function
    End If

    naamdeel = Matches.Item(0).Submatches(deel - 1)
    End Function


    Nu kan je in een cel A2 bijvoorbeeld
    =naamdeel(A1;1)
    doen voor de voornaam,

    = naamdeel(A1;2)
    voor het tussendeel

    =naamdeel(A1;3)
    voor de achternaam

    🙂

  • Dat ik daar niet eerder op ben gekomen;-)

    Ik ga het uitproberen!

  • Gertwin

    Dat is een top oplossing, werkt perfect!!

  • @Gertwin Fijn dat je er wat aan hebt!

  • Ger

    het Tussenvoegsel
    A2 B2 C2 D2
    Frank de Boer Frank de Boer
    Eerst formule B2 dan de formule D2 en daarna de Formule C2 de tussen voegsels

    =DEEL(A2;LENGTE(B2)+2;LENGTE(A2)-LENGTE(B2)+1-LENGTE(D2)-2)

  • Josse Zwols

    Maar wat als iemand Jan Vennegoor Of Hesselink heet?

  • Josse, dat gaat inderdaad nog niet goed (zoals ik onderaan de tekst ook aangeef). Het ging mij er vooral om om lijsten te kunnen sorteren en daarvoor is bovenstaande oplossing voldoende. Misschien dat de oplossing van Jelmer daar nog iets in kan betekenen?

  • Elise Roders

    Josse, dat gaat inderdaad nog niet goed (zoals ik onderaan de tekst ook aangeef). Het ging mij er vooral om om lijsten te kunnen sorteren en daarvoor is bovenstaande oplossing voldoende. Misschien dat de oplossing van Jelmer daar nog iets in kan betekenen?

Familie van i

Samen met Katja Staring verzorg ik workshops voor zelfstandig professionals. Wij helpen je om je website slim in te zetten voor je bedrijf. Dat doen wie via de Familie van i.