Het Excel bestand ‘Data Opschonen’ staat onderaan de tekst. Het mag gratis worden gedownload voor eigen gebruik. Het bestand werkt zowel in de Windows- als MacOS versie van Excel .
Samenvatting
Als je regelmatig gegevens uit externe bronnen importeert of naar Excel kopieert, ben je wellicht bekend met het volgende probleem: vervuilde data. In deze blog leer je met behulp van een aantal basisfuncties in 5 stappen je data opschonen. Achtereenvolgens worden SPATIES.WISSEN, SUBSTITUEREN, LENGTE, VIND.SPEC en LINKS besproken.
- Stap 1: Overbodige spaties verwijderen (SPATIES.WISSEN)
- Stap 2: Overgebleven spaties tellen (SUBSTITUEREN, LENGTE)
- Stap 3: Laatste spatie vervangen door teken (SUBSTITUEREN)
- Stap 4: Positie van vervangingsteken bepalen (VIND.SPEC)
- Stap 5: Gegevens links van vervangingsteken tonen (LINKS)
Brongegevens
Het lijstje met vervuilde adresgegevens vormt de basis. De adressen bevatten te veel spaties en huisnummers staan dubbel vermeld. ‘Data opschonen’ betekent dus dat overbodige spaties moeten worden verwijderd, evenals de dubbele huisnummers.
Stap 1: Overbodige spaties verwijderen
Om overbodige spaties te verwijderen, wordt de functie SPATIES.WISSEN gebruikt. Deze functie verwijdert overbodige spaties uit een tekst. Tevens laat de functie één spatie tussen de woorden staan. Ook ‘onzichtbare’ spaties achter het laatste woord, worden verwijderd.
Voorbeeld
In cel B2 wordt de volgende functie geplaatst:
=SPATIES.WISSEN(A2)
Toelichting
SPATIES.WISSEN(A2) betekent: Verwijder alle overbodige spaties uit de tekst in cel A2.
Stap 2: Overgebleven spaties tellen
Om overgebleven spaties te tellen, wordt een formule gebruikt die in gewoon Nederlands luidt:
Aantal spaties = Lengte van de tekst met spaties – Lengte van de tekst zonder spaties.
Gebruik eerste de functie SUBSTITUEREN om een tekst zonder spaties te maken. Gebruik vervolgens de functie LENGTE om de lengte van deze tekst te bepalen. Gebruik daarna de functie LENGTE om de lengte van de tekst met spaties te bepalen. Trek tenslotte de gevonden tekstlengtes van elkaar af.
Voorbeeld
In cel B2 wordt de volgende functie geplaatst:
=LENGTE(B2)-LENGTE(SUBSTITUEREN(B2;” “;””))
Toelichting
LENGTE(SUBSTITUEREN(B2;” “;””)) betekent: Vervang in de tekst in cel B2 de spaties (” “) door ‘niets’ (“”) en bepaal de overgebleven lengte. De uitkomst is 22.
LENGTE(B2) betekent: Bepaal de lengte van de tekst in cel B2 inclusief spaties. De uitkomst is 26.
De totale uitkomst is 26 – 22 = 4
Stap 3: Laatste spatie vervangen door teken
Om de laatste spatie te vervangen door een speciaal teken, wordt opnieuw de functie SUBSTITUEREN gebruikt. Bij stap 2 is het totaal aantal spaties bepaald. Daardoor is ook bekend welke spatie de laatste spatie is. Als vervangingsteken wordt een @ gebruikt.
Voorbeeld
In cel D2 wordt de volgende functie geplaatst:
=SUBSTITUEREN(B2;” “;”@”;C2)
Toelichting
SUBSTITUEREN(B2;” “;”@”;C2) betekent: Vervang in de tekst in cel B2 de spatie (” “) op positie C2 (positie 4, de laatste spatie) door het @ te
Stap 4: Positie van vervangingsteken bepalen
Om te bepalen waar het vervangingsteken terecht is gekomen, wordt de functie VIND.SPEC gebruikt. Deze functie bepaalt letterlijk de plaats van een opgegeven teken in een tekst. Vervolgens geeft de functie de positie van het teken als getal terug.
Voorbeeld
In cel E2 wordt de volgende functie geplaatst:
=VIND.SPEC(“@”;D2)
Toelichting
VIND.SPEC(“@”;D2) betekent: Zoek in de tekst in cel D2 de positie van het @ teken op. De uitkomst is 23 (= de 23e positie).
Stap 5: Gegevens links van vervangingsteken tonen
Om tenslotte alleen de gegevens links van het vervangingsteken te krijgen, wordt de functie LINKS gebruikt.
Voorbeeld
In cel G2 wordt de volgende functie geplaatst:
=LINKS(D2;E2-1)
Toelichting
LINKS(D2;E2-1) betekent: Neem de tekst in cel D2 en toon daarvan de linker E2-1 karakters. In dit voorbeeld toont Excel daarom de linker 22 karakters (namelijk 23-1, het @ teken zelf moet niet getoond worden!) van de tekst in cel D2.
Tenslotte
Nu de formules in afzonderlijke stappen zijn uitgewerkt, kunnen ze in elkaar worden geschoven tot één lange formule. Deze verwijst direct naar A2, waar de vervuilde tekst staat, en maakt alle tussenstappen overbodig:
=LINKS(SUBSTITUEREN(SPATIES.WISSEN(A2);” “;”@”;LENGTE(SPATIES.WISSEN(A2))-LENGTE(SUBSTITUEREN(SPATIES.WISSEN(A2);” “;””)));VIND.SPEC(“@”;SUBSTITUEREN(SPATIES.WISSEN(A2);” “;”@”;LENGTE(SPATIES.WISSEN(A2))-LENGTE(SUBSTITUEREN(SPATIES.WISSEN(A2);” “;””))))-1)
Na het plaatsen van deze formule kunnen kolom B, C, D, E en F dus worden verwijderd.
