Spaties verwijderen in Excel: TRIM(), SUBSTITUTE() en meer

Ik kreeg ooit een spreadsheet met 500+ items die er prima uitzag, maar de totalen niet goed berekende. Ik wist niet waarom, maar later bleek dat verborgen spaties voor allerlei problemen zorgden. Als je met data in Excel werkt, ben je dit vast weleens tegengekomen. Deze spaties sluipen er vaak in wanneer je data uit andere bronnen importeert.  

In deze gids laat ik je verschillende methoden zien om ongewenste spaties in Excel te verwijderen. Je krijgt stapsgewijze instructies en praktische voorbeelden bij elke methode, zodat je de hoofdpijn bespaart die ik heb gehad. Als je net begint, bekijk dan onze cursus Data-analyse in Excel of de skill track Excel Fundamentals. 

TL;DR

• Verborgen spaties breken formules — leidende, afsluitende of extra spaties zorgen voor VLOOKUP()-mismatches, SUM() die nul teruggeeft en mislukte vergelijkingen.
• TRIM() — gebruik in de meeste gevallen; verwijdert leidende/afsluitende/extra spaties en laat één spatie tussen woorden staan.
• SUBSTITUTE() — gebruik om ALLE spaties te verwijderen, ook tussen woorden, of om specifieke tekens te targeten.
• CLEAN() — combineer met TRIM() om niet-afdrukbare tekens uit geïmporteerde data te strippen.
• Harde spaties? Gebruik =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) — de alles-in-één fix.
• Zoeken & Vervangen (Ctrl+H) — snelste optie om spaties direct in-place te verwijderen, geen hulpkolom nodig.

Inzicht in spaties in Excel

Voordat we spaties verwijderen, is het goed om de soorten spaties te kennen die in je data kunnen sluipen:

• Leidende spaties: Spaties aan het begin van een cel.
• Afsluitende spaties: Spaties aan het einde van een cel.
• Extra spaties tussen woorden: Meer dan één spatie tussen woorden.
• Harde spaties (non-breaking): Speciale spaties die er normaal uitzien maar zich anders gedragen.

Deze spaties kunnen in je data terechtkomen door bestanden te importeren, typefouten, inconsistente opmaak of door te kopiëren en plakken uit andere bronnen.

Soorten spaties. Afbeelding door auteur. 

Verschillende manieren om spaties te verwijderen in Excel

Hoewel spaties misschien klein lijken, kunnen ze grote problemen veroorzaken. Formules werken mogelijk niet, vergelijkingen mislukken en berekeningen kunnen fout uitpakken. Voordat je met oplossingen begint, raad ik het volgende aan:

• Maak een back-up van je data: Maak altijd een kopie van je data voordat je wijzigingen aanbrengt. Zo kun je de originele data herstellen als er iets misgaat.
• Controleer de opmaak: Zorg voor consistente opmaak in je cellen, zoals hoofd-/kleine letters en getalnotatie, voor schonere en betrouwbaardere resultaten.

Laten we nu de beste methoden bekijken om spaties in Excel te verwijderen.

Methode 1: Gebruik de TRIM()-functie om extra spaties te verwijderen

De functie TRIM() verwijdert onnodige spaties, zoals leidende, afsluitende en meerdere spaties tussen woorden, met uitzondering van de enkelvoudige spatie. Voor een volledige referentie, bekijk onze gids voor de TRIM()-functie in Excel. Zo gebruik je hem:

1. Maak een hulpkolom naast je data.

2. Voer in de eerste cel van de hulpkolom de formule in: =TRIM(A2).

3. Druk op Enter en sleep de formule omlaag om deze op alle rijen toe te passen.

Gebruik TRIM() om spaties te verwijderen. Afbeelding door auteur.

Tip — vervang de originele data: Nadat je TRIM() in de hulpkolom hebt toegepast, kopieer je de opgeschoonde cellen, klik je met de rechtermuisknop op de originele kolom en kies je Plakken speciaal > Alleen waarden (sneltoets: Alt+E+S+V of Ctrl+Shift+V op Mac). Dit plakt alleen de opgeschoonde waarden, zodat je de hulpkolom daarna kunt verwijderen.

Methode 2: Gebruik de SUBSTITUTE()-functie

De functie SUBSTITUTE() verwijdert elke spatie (ook de spatie tussen woorden) uit een cel en vervangt elk teken, inclusief spaties, door iets anders. Handig voor numerieke data, zoals het weghalen van spaties tussen cijfers. 

De basis-syntaxis is:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Zo pas je het toe om alle spaties te verwijderen: 

1. Maak een hulpkolom.

2. Voer de formule in: =SUBSTITUTE(A2," ","").

3. Druk op Enter en sleep omlaag om het op alle rijen toe te passen.

De functie SUBSTITUTE() gebruiken. Afbeelding door auteur.

Methode 3: Data opschonen met Zoeken & Vervangen

Wil je een snelle en eenvoudige fix, dan raad ik Excel’s Zoeken & Vervangen-tool aan — die kan extra spaties tussen woorden aanpakken. Zo doe je dat:

1. Selecteer je datagebied.

2. Druk op Ctrl+H om het dialoogvenster Zoeken & Vervangen te openen.

3. Voer in het veld Zoeken naar twee spaties in.

4. Voer in het veld Vervangen door één spatie in.

5. Klik op Alles vervangen.

Spaties opschonen met de functie Zoeken & Vervangen. Afbeelding door auteur.

Herhaal de stappen totdat Excel de melding laat zien: We couldn't find anything to replace. Let op: deze methode reduceert leidende en afsluitende spaties tot één spatie, maar verwijdert ze niet volledig. Het pakt alleen spaties tussen woorden aan. 

Methode 4: Gebruik de CLEAN()-functie om niet-afdrukbare tekens te verwijderen

Wanneer we data uit externe bronnen importeren of kopiëren, krijgen we vaak niet-afdrukbare tekens die gegevensbewerkingen verstoren. Je kunt de CLEAN()-functie gebruiken om ze te verwijderen of — nog beter — combineer hem met TRIM() om extra spaties op te ruimen.

Zo pak je het aan: 

1. Voer de formule in: =TRIM(CLEAN(A2)) in een hulpkolom.

2. Druk op Enter en sleep de formule omlaag.

Combineer TRIM() en CLEAN() om spaties te verwijderen. Afbeelding door auteur.

Methode 5: Geavanceerde methode — Excel-add-ins gebruiken voor snellere resultaten

Je kunt de externe add-in Ablebits Ultimate Suite for Excel gebruiken. De tool Trim Spaces ruimt data snel op. Met één klik regelt hij alles: leidende en afsluitende spaties, regeleinden, extra spaties tussen woorden en harde spaties. Werk je, net als ik, regelmatig met grote datasets, dan bespaart dit je veel tijd. 

Zo gebruik je het:

1. Selecteer het bereik cellen dat je wilt opschonen.

2. Open het tabblad Ablebits Data en klik op Trim Spaces.

3. Selecteer in het optiepaneel wat je wilt verwijderen en klik op Trim.

Spaties trimmen met Excel’s add-in. Afbeelding door auteur.

Specifieke oplossingen voor veelvoorkomende scenario's bij spaties verwijderen in Excel

Laten we nu kijken naar een paar veelvoorkomende scenario’s waar je tegenaan kunt lopen en hoe je die snel oplost.

Extra spaties verwijderen

Extra spaties maken je data rommelig en lastig om mee te werken. Je kunt de functie TRIM() gebruiken om ze op te schonen, terwijl er één spatie tussen woorden blijft staan. Ik had bijvoorbeeld een paar data-items met inconsistente spatiëring. Om dit op te lossen, voerde ik de volgende formule in cel B2 in en paste die toe op alle rijen:

=TRIM(A2)

Gebruik TRIM() om extra spaties te verwijderen. Afbeelding door auteur.

Leidende spaties verwijderen 

Soms moeten we meerdere spaties tussen woorden behouden om data beter leesbaar te maken, maar willen we spaties aan het begin verwijderen. De functie TRIM() werkt goed, maar haalt alle extra spaties in de tekst weg. In zo’n geval, waarin je alleen de leidende spaties wilt verwijderen en de rest intact wilt laten, gebruik je deze geavanceerde formule:

=MID(A2,FIND(MID(TRIM(A2),1,1),A2),LEN(A2))

Zo werkt de formule: hij lokaliseert het eerste niet-spatieteken en behoudt alles vanaf dat punt. Zo blijven spaties tussen woorden behouden en worden alleen de spaties aan het begin van de cel verwijderd (leidende spaties).

Alleen leidende spaties verwijderen. Afbeelding door auteur.

Afsluitende spaties verwijderen

Afsluitende spaties zijn extra spaties die na tekst of getallen in een cel verschijnen. Deze onzichtbare tekens kunnen problemen veroorzaken, zoals formulefouten waarbij cellen die identiek lijken, niet overeenkomen in functies als VLOOKUP() of MATCH(). Gebruik hiervoor de functie TRIM(). Ik zette bijvoorbeeld deze formule in cel B2 en paste hem vervolgens toe op alle rijen om extra spaties uit mijn data te halen.

=TRIM(A2)

Afsluitende spaties verwijderen. Afbeelding door auteur.

Spaties vóór getallen verwijderen

Hoewel TRIM() prima werkt om spaties in tekst op te schonen, kun je bij numerieke data tegen problemen aanlopen. Dit gebeurt er:

=TRIM(A2)

De functie TRIM() verwijdert de spaties niet correct. Afbeelding door auteur.

Je ziet dat TRIM() spaties vóór een getal verwijdert, maar Excel ziet het resultaat niet meer als getal — het wordt behandeld als tekst. Zo herken je dat:

• De data is links uitgelijnd in plaats van rechts.

• Kijk naar je statusbalk — die toont alleen hoeveel cellen je hebt geselecteerd (COUNT), niet hun totaal.

• Als je deze cellen probeert op te tellen met SUM(), krijg je nul.

Je moet de tekst weer omzetten naar een getal om dit te herstellen. Dat kan door te vermenigvuldigen met 1 of met deze formule:

=VALUE(TRIM(A2))

Dit verwijdert spaties en zorgt dat Excel de data als getallen behandelt.

Combineer VALUE() en TRIM() om spaties vóór getallen te verwijderen. Afbeelding door auteur.

Spaties uit een tekenreeks verwijderen 

Wil je alle spaties verwijderen, ook die tussen woorden, gebruik dan de functie SUBSTITUTE():

=SUBSTITUTE(A2, " ", "")

Je ziet dat de formule elke spatie uit de tekenreeks haalt.

Verwijder spaties met de functie SUBSTITUTE(). Afbeelding door auteur.

Speciale tekens en onnodige spaties verwijderen  

SUBSTITUTE() is ideaal als je specifieke tekens uit je tekst wilt verwijderen. Je kunt het echter combineren met TRIM() om in één keer speciale tekens en spaties op te schonen. Wil ik bijvoorbeeld & verwijderen en spaties opschonen, dan gebruik ik deze formule:

=TRIM(SUBSTITUTE(A2, "&", ""))

Je ziet hoe TRIM() en SUBSTITUTE() samen werken versus SUBSTITUTE() alleen.

Combineer TRIM() en SUBSTITUTE() om spaties op te schonen. Afbeelding door auteur.

Regeleinden verwijderen

Soms bevat data die van het web of andere bronnen is geplakt regeleinden (Alt+Enter-tekens, CHAR(10)) die op extra spaties lijken. De functie TRIM() pakt deze niet mee. Zo verwijder je ze:

1. Selecteer de cellen die je wilt opschonen.

2. Druk op Ctrl+H om Zoeken & Vervangen te openen.

3. Druk in het veld Zoeken naar op Ctrl+J (er verschijnt een knipperende cursor — dat is het regeleindteken).

4. Laat het veld Vervangen door leeg (of typ een spatie om woorden niet te laten samensmelten).

5. Klik op Alles vervangen.

Of gebruik de formule: =TRIM(CLEAN(A1)) — de functie CLEAN() stript regeleinden (CHAR(10)) en andere niet-afdrukbare tekens (ASCII 1–31) in één stap.

Veelvoorkomende problemen en oplossingen

Soms, wanneer ik data van andere bronnen kopieer of importeer, verschijnen er harde spaties en vreemde tekens in mijn data. Zo pak ik dat aan: 

Harde spaties die TRIM() niet verwerkt

De functie TRIM() verwijdert extra spaties, maar werkt niet voor harde spaties (tekencode 160). Die komen vaak voor in data van webpagina’s of opgemaakte bronnen. Om dit te fixen, gebruik ik:

=TRIM(SUBSTITUTE(A1, CHAR(160), ""))

Deze formule verwijdert harde spaties volledig. Wil je ze eerst vervangen door gewone spaties (zodat TRIM() eventuele extra’s kan opruimen), gebruik dan: =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) — de gouden standaard om alle spatie-typen in één keer te strippen.

Verborgen tekens of problemen met geplakte data

Soms sluipen er andere verborgen tekens in, zoals tabs of regeleinden. Om ze te vinden, gebruik ik CODE() om de positie van het teken te checken:

• Eerste teken: =CODE(LEFT(A1,1))

• Laatste teken: =CODE(RIGHT(A1,1))

• Specifieke positie: =CODE(MID(A1, n, 1)) (Vervang n door het positienummer.)

Dit is de formule die ik gebruik:

=CODE(RIGHT(C2,1))

Zodra ik de code weet, verwijder ik hem met TRIM() en SUBSTITUTE(). Om bijvoorbeeld tabs (code 115) te verwijderen, gebruik ik:

(SUBSTITUTE(C2, CHAR(115), “ “))

Gebruik CODE(), TRIM() en SUBSTITUTE() om spaties te verwijderen. Afbeelding door auteur.

Snelle methoden vergeleken

Weet je niet welke methode je moet gebruiken? Deze tabel koppelt elk scenario aan het juiste hulpmiddel:

Tot slot

In deze gids liet ik je methoden zien zoals TRIM() voor eenvoudige fixes, SUBSTITUTE() voor complexere problemen en add-ins voor wanneer je vaak spaties moet verwijderen en een andere workflow wilt. Elke methode past bij verschillende datatypen, dus kies wat bij jouw situatie past. Voor een bredere blik op het schoon houden van je spreadsheets, zie onze gids voor data opschonen in Excel.

Wil je je skills verder versterken, bekijk dan de DataCamp-skill track Data-analyse met Excel Power Tools. Die biedt stapsgewijze lessen om Excel in de vingers te krijgen en je technieken voor data opschonen te verbeteren.