Fatsoeneren Excel-bestand (tekst naar kolommen)

Moet jij soms (of regelmatig) adreslijsten maken? Hoe is dat voor jou? Een simpel klusje tussendoor of een hoofdpijnproject van enkele dagen?

Meestal worden de gegevens voor adresbestanden geëxporteerd uit een cms-systeem. Of jij hier vervolgens lang of kort mee bezig bent, hangt voor een groot deel af van de manier waarop de gegevens in het cms-systeem opgeslagen zijn. Hoe dan ook: waarschijnlijk ga jij veel tijd besparen met een speciaal hiervoor bedoelde functie in Excel. En gebruik jij Office 2013 of nieuwer? Dan laat ik je graag een nieuwe functie zien.

Tekst naar kolommen
(gescheiden door vast teken)

Heb je gegevens in één kolom staan die gescheiden zijn door een vast teken, bijvoorbeeld een komma? Dan kun je die gegevens in Excel eenvoudig splitsen naar meerdere kolommen. Stel dat je de straat, postcode en woonplaats in één kolom hebt staan, maar ze zijn wel gescheiden door een komma. Zorg dan dat je naast de kolom waar de gegevens in staan, nog twee lege kolommen hebt. Of meer, afhankelijk van het aantal kolommen waar je de gegevens naar wilt splitsen.

Nu selecteer je de kolom die je wilt splitsen (je kunt slechts één kolom per keer splitsen) of een aantal cellen. Daarna ga je naar het tabblad Gegevens, bij Hulpmiddelen voor gegevens kies je voor Tekst naar kolommen.

Wizard Tekst naar kolommen (1)

In de Wizard die je nu krijgt, kies je voor gescheiden. Dit houdt in dat er een scheidingsteken tussen de gegevens staat. Daarna kies je voor Volgende. Nu geef je aan welk scheidingsteken gebruikt is (je kunt ook scheidingstekens combineren). Je kunt kiezen tussen enkele veelvoorkomende tekens, of bij Overige zelf een teken invoeren. Daarna klik je weer op Volgende. Bovenaan kun je per kolom een opmaak instellen. Door in het voorbeeld op de volgende kolom te klikken, stel je daar een opmaak voor in. Dit kan natuurlijk ook achteraf op de wijze waarop je dat gewend bent.

Naast Bestemming kun je aangeven waar de gegevens geplaatst moeten worden. Je kunt dit intypen of op de knop geheel rechts klikken en dan de juiste cellen kiezen. Omdat wij al lege kolommen naast de te verdelen kolom hebben geplaatst, hoef je hier echter niets aan te passen. Bij het voorbeeld kun je kijken of de gegevens juist gescheiden worden. Klik op voltooien om de wizard af te sluiten en de tekst te splitsen. Let op als je de melding “Er bevinden zich hier al gegevens. Wilt u deze vervangen?” krijgt. Je hebt dan onvoldoende lege kolommen toegevoegd en er worden gegevens overschreven. Dit wil je waarschijnlijk niet, dus klik je op Annuleren. Probeer het opnieuw nadat je lege kolommen hebt toegevoegd of kies een andere bestemming.

Tekst naar kolommen (vaste breedte)

Je kunt ook gegevens splitsen op basis van een vaste breedte, zoals bijvoorbeeld bij de postcode en de plaats.

Het begin is dan hetzelfde als bij “gescheiden door vast teken”. Bij stap 1 van de Wizard kies je nu echter voor Vaste breedte, daarna klik je op Volgende. Je kunt nu aangeven waar de kolom gesplitst moet worden door op die positie in het voorbeeld te klikken. Verplaatsen een lijn door deze te verslepen, verwijder een lijn door erop te dubbelklikken. Je kunt meerdere lijnen plaatsen. Het vervolg gaat weer zoals bij “gescheiden door vast teken”.

Wizard Tekst naar kolommen (2)

Extra spaties verwijderen

Is het splitsen goed gegaan maar staan er nog ongewenste spaties in de cellen? Dat los je eenvoudig op met de formule Spaties wissen. Typ in een cel =spaties.wissen(locatie). Op de plek waar nu locatie staat, typ je de cel, dus bijvoorbeeld C1. Nu worden de spaties uit een tekst gewist, behalve de spaties tussen de woorden. Deze formule sleep je met de vulgreep naar de overige cellen in de kolom. Ideaal toch? Als je het adressenbestand wilt gebruiken voor andere doeleinden, kan het lastig zijn dat er formules in die cellen staan in plaats van het eindresultaat. Dit los je eenvoudig op door de kolom/cellen te kopiëren en in een nieuwe kolom de waarden te plakken (tabblad Start, bij Klembord, kiezen voor de pijl onder Plakken en vervolgens een van de opties bij Waarden plakken kiezen). Nu kun je de andere kolommen verbergen of verwijderen.

Alles in hoofdletters (of juist niet)

Als je alles in hoofdletters wilt, hanteer je dezelfde werkwijze als bij “Extra spaties wissen”. Nu gebruik je echter de formule =hoofdletters(locatie). Natuurlijk typ je op de plek waar nu locatie staat, weer de juiste cel. Liever beginletters of kleine letters? Gebruik dan =beginletters(locatie) of =kleine.letters(locatie).

Flash Fill

In Excel 2013 is er een nieuwe functie gekomen, genaamd Flash Fill. Hiermee kun je gegevens nog eenvoudiger fatsoeneren.

Flash Fill (1)

Zoals je hierboven ziet heb ik een kolom waarin het adres staat, maar dit staat nu in één kolom en ik wil dit netjes in gescheiden in aparte kolommen hebben. Nu typ ik het eerste adres goed in.

Flash Fill (2)

Vervolgens selecteer ik de cellen in kolom B, waar het adres moet komen te staan (inclusief het eerste adres dat ik al zelf ingetypt heb) en klik ik op het tabblad Gegevens bij Hulpmiddelen voor gegevens op Flash Fill. De overige gegevens worden ingevuld. Daarna doe ik hetzelfde met kolom C en D.

Flash Fill (3)

Nu zijn de kolommen netjes gescheiden en ik heb er bijna geen werk mee gehad. Wel zou ik het bestand nog even nalopen om te controleren of alles inderdaad zo staat als het moet staan. Het hangt een beetje van de gegevens af of Excel direct het verband kan zien of dat je misschien meer voorbeelden zelf in moet vullen. Afhankelijk van de basisgegevens kan het ook zijn dat Excel automatisch al met een voorstel komt, nog voor je op de functie Flash Fill klikt. Dan hoef je alleen maar op Enter te drukken om de gegevens te bevestigen – nog eenvoudiger dus.

Natuurlijk kun je deze functie ook voor veel andere gegevens gebruiken. Ik ben erg blij met deze nieuwe functie, het scheelt zeeën van tijd. Is het jou ook gelukt?

Op de hoogte blijven?

3 gedachten over “Fatsoeneren Excel-bestand (tekst naar kolommen)

  1. Dit is zo super helpfull, dank je wel. Eerder zat ik ook te knutselen met een A-4tje met gegevens die op een lijst moesten komen en deze manier is zo handig en ook heel duidelijk weergegeven. Natuurlijk kan ik ook zelf dingen uitvogelen, maar op zo’n moment komt een hulpvraag en de verwachting is dan ‘dat je dat wel even doet’ . Het volgende waar ik dan ook mee zat in Excel is filters aanmaken, zodat de gegevens die in invoert / aanmaakt niet direct zichtbaar zijn, maar wel voor handen zijn. Kun je dat ook eens behandelen? Super veel dank daarvoor. Met hartelijke groet, Irene

    • Hoi Irene,

      Zo gaat het vaak, je moet het ‘even’ uitzoeken en dat komt er dan niet van. Nu kun je lezen hoe het moet :-).
      Een blog over filters in Excel zal ik zeker toevoegen aan mijn lijstje met onderwerpen.

      Bedankt voor je reactie!

      Met vriendelijke groet,
      Joyce

Geef een reactie

%d bloggers liken dit: