CLEAN()-functie in Excel: de beste manier om niet-afdrukbare tekens te verwijderen

Heb je ooit data in Excel geïmporteerd en gemerkt dat vreemde, verborgen tekens je werk in de war schoppen? Dit zijn niet-afdrukbare tekens die opmaakproblemen veroorzaken en zelfs berekeningen kunnen verpesten.  

Je kunt zulke ongewenste tekens verwijderen met de functie CLEAN(). In dit artikel laat ik je zien wat CLEAN() doet, wanneer je het gebruikt en hoe het je werk makkelijker maakt.

Snel antwoord: de Excel-functie CLEAN()

Zo gebruik je de CLEAN-functie 

• Typ =CLEAN(.

• Voer de tekst in die je wilt opschonen of de celverwijzing.

• Sluit de functie af met ).

• Druk op Enter.

=CLEAN(text)Mogelijk gemaakt door

text kan elke tekenreeks zijn die je wilt opschonen.

=CLEAN(A1)Mogelijk gemaakt door

A1 kan worden vervangen door een andere celverwijzing.

Een voorbeeld van de CLEAN()-functie in actie

Laten we iets uitgebreider bekijken hoe we de functie CLEAN() kunnen gebruiken. Dit keer voeg ik wat screenshots toe.

Hier staat data in cel A2 met wat ongewenste tekens. Daarom voer ik de volgende formule in cel B2 in om mijn tekst op te schonen:

=CLEAN(A2)Mogelijk gemaakt door

Gebruik de functie CLEAN() in één cel. Afbeelding door de auteur.

Naast het opschonen van één cel kun je de functie CLEAN() ook gebruiken om meerdere cellen tegelijk op te schonen. Alles wat je hoeft te doen is, nadat je de formule in de juiste cel hebt geschreven, het kleine groene vierkantje in de hoek van de cel naar de laatste rij slepen. Zo wordt de formule op de hele dataset toegepast en heb je binnen enkele seconden nette data. 

Meerdere cellen opschonen met de functie CLEAN(). Afbeelding door de auteur.

Ik vind de functie CLEAN() vooral nuttig na het importeren van data uit externe bronnen zoals databases of webpagina’s. Bij het importeren kunnen namelijk niet-afdrukbare tekens met ASCII-codes 0–31 meekomen. Ook al zie je ze niet, ze kunnen berekeningen verstoren of je data er vreemd uit laten zien. 

De CLEAN()-functie van Excel verwijdert deze ongewenste tekens. Ze haalt ook overbodige regeleinden en andere verborgen tekens weg die opmaakproblemen kunnen veroorzaken. Soms is het dus handig om CLEAN() te gebruiken, zelfs als je geen duidelijke problemen ziet.

Excel CLEAN() vs. TRIM()

Als je een van onze cursussen hebt gevolgd, zoals Excel Fundamentals, ken je misschien nog een andere veelgebruikte Excel-functie, de TRIM()-functie.

Hoewel de functies CLEAN() en TRIM() allebei tekst opschonen, werken ze verschillend. CLEAN() verwijdert niet-afdrukbare tekens (ASCII 0–31) en TRIM() verwijdert extra spaties (ASCII 32) voor, na of tussen woorden (met handig genoeg één spatie tussen woorden als uitzondering). 

Met andere woorden: gebruik CLEAN() om data met verborgen opmaakproblemen op te schonen, en als je data onregelmatige spatiëring heeft, kun je dat herstellen met de functie TRIM().

Later laat ik zien hoe je CLEAN() kunt combineren met TRIM() om zowel niet-afdrukbare tekens te verwijderen als overbodige spaties te elimineren.

CLEAN() combineren met andere Excel-functies 

Nu je weet wat de functie CLEAN() is, kijken we waar je dit in de praktijk kunt gebruiken:

CLEAN() combineren met TRIM() 

Zoals je in het bovenstaande voorbeeld ziet, verwijderde CLEAN() de vreemde spatiëring tussen de tekst niet. Om dit te verhelpen, plaats ik het binnen de functie TRIM(), en je ziet dat de data in kolom B nu netjes is opgemaakt:

=TRIM(CLEAN(A2))Mogelijk gemaakt door

Combineer de functies TRIM() en CLEAN(). Afbeelding door de auteur.

CLEAN() gebruiken met SUBSTITUTE()

Er zijn enkele niet-afdrukbare unicode-tekens buiten de eerste 32 ASCII-tekens, en CLEAN() kan die niet alleen aan. Maar als we de functie SUBSTITUTE() combineren met CLEAN(), kunnen we ze eenvoudig verwijderen. Laten we dit begrijpen met een voorbeeld:

Ik heb willekeurige tekst in cel A2 waar een spatiëringsprobleem aan de linkerkant van de tekst zit. Maar toen ik het probeerde te verwijderen met TRIM(), lukte dat niet. Het lijkt dus op een spatie, maar dat is het niet — het is een speciaal teken. Bepaal in zulke gevallen eerst de tekencode met de volgende formule:

=CODE(LEFT(A2,1))Mogelijk gemaakt door

Dit geeft de tekencode, namelijk 160. Je kunt die vervolgens verwijderen met de volgende formule:

=SUBSTITUTE(CLEAN(A2),CHAR(160),"")Mogelijk gemaakt door

Voor een schonere tekst wikkel je de bovenstaande formule in TRIM().

=TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160),""))Mogelijk gemaakt door

Cellen opschonen met CLEAN() en SUBSTITUTE(). Afbeelding door de auteur.

CLEAN() gebruiken met REPLACE()

Als we een andere scheidingsteken willen vervangen en tegelijk niet-afdrukbare tekens willen verwijderen, kunnen we de functie CLEAN() combineren met de functie REPLACE(). De functie REPLACE() in Excel vervangt een deel van een tekst door nieuwe tekst op basis van een opgegeven startpositie en lengte. 

=REPLACE(old_text, start_num, num_chars, new_text)Mogelijk gemaakt door

Als ik bijvoorbeeld het -  teken wil vervangen door een spatie en alle niet-afdrukbare tekens wil verwijderen, gebruik ik de volgende formule:

=REPLACE(CLEAN(A2), 4, 1, " ")Mogelijk gemaakt door

Deze formule maakt eerst de oude tekst schoon en vervangt vervolgens het - op positie 4 door een spatie.

Tekst opschonen met REPLACE() en CLEAN(). Afbeelding door de auteur.

CLEAN() gebruiken met REPT()

We kunnen de functie REPT() ook combineren met CLEAN() om tekst op te schonen. De functie REPT() herhaalt een tekst een opgegeven aantal keren. 

=REPT(text, number_of_times)Mogelijk gemaakt door

Ik pas bijvoorbeeld de volgende formule toe op mijn dataset: 

=TRIM(CLEAN(SUBSTITUTE(A2, " ", REPT(" ", 2))))Mogelijk gemaakt door

Hier vervangt SUBSTITUTE() elke enkele spatie door twee spaties met REPT(" ", 2), waardoor de spatiëring consistenter wordt. Vervolgens verwijdert CLEAN() verborgen niet-afdrukbare tekens en haalt TRIM() extra spaties weg, zodat er slechts één spatie tussen woorden overblijft.

Tekst opschonen met REPT() en CLEAN(). Afbeelding door de auteur.

Zoals je ziet, komt CLEAN() op veel plekken van pas en werkt het goed samen met veel andere functies.

Een paar aandachtspunten

Hoewel CLEAN() en SUBSTITUTE() geweldig zijn om ongewenste tekens te verwijderen, kunnen we soms per ongeluk iets belangrijks weghalen als we niet goed opletten. Bijvoorbeeld: 

• Per ongeluk dataverlies: Vermijd te brede formules. Als we alle spaties vervangen door niets, vloeien woorden samen. En als we alle speciale tekens verwijderen, kan onze formule streepjes uit telefoonnummers of decimalen uit prijzen wissen. Controleer dus voordat je een functie toepast of de formule correct werkt. 
• Test op subsets: Voor de zekerheid test ik mijn formule altijd eerst op een kleine dataset. In plaats van hem in één keer op duizenden rijen toe te passen, probeer ik hem op een paar rijen en kijk ik of het werkt zoals verwacht. Als alles er goed uitziet, pas ik hem pas toe op de hele dataset.

Tot slot

De functie CLEAN() schoont tekstdata snel op door niet-afdrukbare tekens te verwijderen. Op zichzelf zorgt ze voor schone en gestructureerde data. In combinatie met de functie TRIM() verbetert ze de leesbaarheid doordat ook onnodige spaties verdwijnen.

Wil je meer Excel-tips en -trucs leren? We hebben een paar uitstekende cursussen voor je. Begin met de skill track Excel Fundamentals om de basis te leren en ga daarna verder met de cursussen Data Analysis in Excel en Data Preparation in Excel om je skills verder te verfijnen.