Patronen vervangen met REGEXREPLACE

Een veelgebruikte truc is tekens verwijderen door ze te vervangen door een lege tekenreeks "".

De onderstaande formule verwijdert alles wat geen cijfer is, zodat alleen de getallen overblijven. (555) 123-4567 wordt 5551234567. De klasse [^\d] betekent elk teken dat geen cijfer is, en + selecteert reeksen daarvan in één keer.

Geïmporteerde tekst bevat vaak dubbele of driedubbele spaties. Je wilt elke reeks spaties terugbrengen tot één enkele spatie.

De onderstaande formule zoekt naar een of meer witruimtetekens en vervangt elke reeks door één spatie. Combineer de formule later met TRIM om ook de uiteinden op te schonen.

De vervangende tekst kan met $1, $2 enzovoort terugverwijzen naar vastgelegde groepen. $1 betekent wat de eerste vastleggroep heeft gevonden.

Zo kun je de volgorde wijzigen of tekst opnieuw opmaken. De onderstaande formule verandert Smith, John in John Smith door de twee namen vast te leggen en hun volgorde om te draaien.

Stel dat A2 een tekenreeks met tien cijfers bevat, zoals 5551234567, en dat je (555) 123-4567 wilt.

Leg drie cijfers, nog drie cijfers en vier cijfers vast en bouw daarna de opmaak in de vervanging opnieuw op. Naar elke groep verwijs je met het bijbehorende nummer.

Je kunt ook letterlijke woorden vervangen. Als je afkortingen wilt standaardiseren, zoek je het woord op en vervang je het.

De onderstaande formule verandert elke St. in Street in een adres. Denk eraan de punt te escapen als \., zodat die overeenkomt met een letterlijke punt in plaats van met een willekeurig teken.

Reguliere expressies zijn standaard hoofdlettergevoelig. Als je een woord ongeacht de hoofdletters wilt vinden, vermeld je de varianten in een klasse of bouw je alternatieven op.

De onderstaande formule vervangt color of Color door colour. [Cc] komt overeen met een van beide hoofdlettervarianten van de eerste letter.

Als je alle cijfers behalve de laatste vier van een kaartnummer wilt verbergen, vervang je de eerste cijfers door X.

De onderstaande formule zoekt alle cijfers vóór de laatste vier en vervangt elk cijfer door een X. Voor 1234567890123456 krijg je XXXXXXXXXXXX3456.

Voor echte opschoonacties zijn vaak meerdere stappen nodig. Je kunt REGEXREPLACE-aanroepen nesten, waarbij het resultaat van de binnenste aanroep naar de buitenste gaat.

De onderstaande formule verwijdert eerst alles wat geen letter of spatie is en brengt daarna meerdere spaties terug tot één. Als je de formule van binnen naar buiten leest, zie je de volgorde van de bewerkingen.

Net als de andere functies voor reguliere expressies is REGEXREPLACE bedoeld om een hele kolom in één keer op te schonen. Je schrijft de formule één keer en vult die naast je onbewerkte gegevens naar beneden.

Stel dat kolom A productnamen bevat met achteraan rommelige codes, zoals Chair (SKU-4821). De onderstaande formule verwijdert de code tussen haakjes uit elke rij, zodat alleen Chair overblijft. Zet er TRIM omheen om de overgebleven spatie te verwijderen.

Je hebt geleerd dat REGEXREPLACE(text, pattern, replacement) elke overeenkomst in Google Sheets herschrijft. De belangrijkste punten:

• Vervang door "" om ongewenste tekens te verwijderen
• [^\d] en \s+ zijn belangrijke hulpmiddelen voor het opschonen
• Vastleggroepen kunnen in de vervanging opnieuw worden gebruikt als $1 en $2
• Escap speciale letterlijke tekens zoals \.
• Nest aanroepen om meerdere opschoonacties aan elkaar te koppelen

Hierna combineer je reguliere expressies met SPLIT en CLEAN om geïmporteerde gegevens op te splitsen en verder op te schonen.