Tekstfuncties en zoeken in 1 cel

Voorbeeldbestanden bij dit artikel:
  • Karakters tellen
  • Karakters tellen
  • Functies heb je in Excel in alle maten en kleuren. Van de overbekende SOM, AANTAL, GEMIDDELDE, … functies, over datum- en tijdsfuncties, wiskundige functies, financiële functies, zoekfuncties, tekstfuncties, logische functies en databasefuncties. Voorts heb je nog extra functies die beschikbaar zijn als je de invoegtoepassing Analysis Toolpak inlaadt, en bevat VBA massa's functies. Ook zijn er zeer interessante matrixformules. En eigenlijk zijn de mogelijkheden virtueel onbeperkt, aangezien je je eigen functies (User Defined Functions of UDF) kan programmeren in een module in VBA (RomanToArabic is een voorbeeld van een UDF). Deze pagina geeft de meest courante tekstfuncties in een praktisch voorbeeld. Geen droge uitleg zoals in de helpfiles. Tevens behandel ik een aantal functies voor het zoeken binnen 1 cel. Ik maak meestal voor de gemakkelijkheid gebruik van cel A1. Pas aan indien nodig.

    Algemeen

    Aantal karakters in een cel: =LENGTE(A1)

    De karakters 5 tot 7 ophalen (dus 3 in totaal): =DEEL(A1;5;3)

    De letter a 12 keer herhalen in een cel: =HERHALING("a";12)

    5 spaties zetten in een cel: =HERHALING(" ";5)

    Een cel aanvullen met x-jes tot er minstens 20 karakters zijn:

    =ALS(LENGTE(A1)<20;A1 & HERHALING("x";20-LENGTE(A1));A1)

    Of: =A2 & HERHALING("x";MAX(0;20-LENGTE(A2)))

    Samenvoegen en aanvullen

    Cellen samenvoegen - methode 1: =A1&B1&A2&B2

    Cellen samenvoegen - methode 2:

    =TEKST.SAMENVOEGEN(A1;B1;A2;B2)

    Cellen samenvoegen met spaties ertussen:

    =A1&" "&B1&" "&A2&" "&B2

    Alle spaties verwijderen, behalve de enkele spaties tussen karakters:

    =SPATIES.WISSEN(A1)

    Tekst zoeken

    Zit er een a in de cel - methode 1:

    =ALS(ISFOUT(VIND.ALLES("a";A1));"Neen";"Ja")

    Zit er een a in de cel - methode 2:

    =ALS(LENGTE(A1)>LENGTE(SUBSTITUEREN(A1;"a";""));"Ja";"Neen")

    De plaats van de eerste a: =VIND.ALLES("a";A1)

    De plaats van de eerste A: =VIND.ALLES("A";A1)

    De plaats van de eerste a of A - methode 1:

    =VIND.ALLES("A";HOOFDLETTERS(A1))

    De plaats van de eerste a of A - methode 2: =VIND.SPEC("a";A1)

    Alternatief: =VIND.SPEC("A";A1), want VIND.SPEC is hoofdletterongevoelig

    De plaats van de eerste a, ná het tiende karakter:

    =VIND.ALLES("a";A1;10)

    De plaats van de tweede a - methode 1: zoek a na de eerste a:

    =VIND.ALLES("a";A1;VIND.ALLES("a";A1)+1)

    De plaats van de tweede a - methode 2: zoek a in wat overblijft vanaf de eerste a (RECHTS functie kan ook ipv. DEEL):

    =VIND.ALLES("a";A1)+VIND.ALLES("a";DEEL(A1;VIND.ALLES("a";A1)+1;300))

    De plaats van de tweede a - methode 3: substitueer de tweede a door een speciaal karakter (bv. ^), en zoek dan de plaats van ^ op:

    =VIND.ALLES("^";SUBSTITUEREN(A1;"a";"^";2))

    De plaats van de laatste a, je weet niet hoeveel het er zijn:

    =VIND.ALLES("^";SUBSTITUEREN(A1;"a";"^";LENGTE(A1)-LENGTE(SUBSTITUEREN(A1;"a";""))))

    De plaats van de laatste spatie, je weet niet hoeveel het er zijn:

    =VIND.ALLES("^";SUBSTITUEREN(A1;" ";"^";LENGTE(A1)-LENGTE(SUBSTITUEREN(A1;" ";"")))) Test wel ook of er wel een spatie in de tekst zit, anders krijg je een foutmelding. Opdat er 1 of meer spaties in de tekst zouden zitten, mag LENGTE(A1)-LENGTE(SUBSTITUEREN(A1;" ";"")) niet 0 zijn.

    Alles na de laatste \:

    =SPATIES.WISSEN(RECHTS(SUBSTITUEREN(A1;"\";HERHALING(" ";LENGTE(A1)));LENGTE(A1)))

    Of als je een maximale lengte van 10 karakters in gedachten hebt voor hetgeen na de laatste \ komt:

    =SPATIES.WISSEN(RECHTS(SUBSTITUEREN(A1;"\";HERHALING(" ";10));10))

    Alternatief:

    =RECHTS(A1;LENGTE(A1)-VIND.ALLES(TEKEN(1);SUBSTITUEREN(A1;"\";TEKEN(1);LENGTE(A1)-LENGTE(SUBSTITUEREN(A1;"\";"")))))

    ENKEL het laatste - in een cel wissen, de andere - niet:

    =SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(A1;"-";"";LENGTE(A1)-LENGTE(SUBSTITUEREN(A1;"-";"")));"-";"");"";"-") Voor een uitleg hierover, lees hier.

    Hoofdletters en kleine letters

    Celinhoud in hoofdletters zetten: =HOOFDLETTERS(A1)

    Celinhoud in kleine letters zetten: =KLEINE.LETTERS(A1)

    Celinhoud zoals in een zin zetten: =BEGINLETTERS(A1)

    Bevat een cel één of meer kleine letters?

    =ALS(GELIJK(A1;HOOFDLETTERS(A1));"Neen";"Ja")

    Bevat een cel één of meer hoofdletters?

    =ALS(GELIJK(A1;KLEINE.LETTERS(A1));"Neen";"Ja")

    Cellen splitsen

    Splitsen op een liggend streepje: eerste deel:

    =LINKS(A1;VIND.ALLES(" ";A1)-1)

    Splitsen op een liggend streepje: tweede deel:

    =RECHTS(A1;LENGTE(A1)-VIND.ALLES("-";A1))

    Noot: de functies Split en Join in VBA zijn handiger voor meerdere splitsingen.

    Met de DEEL functie: eerste deel: =DEEL(A1;1;VIND.ALLES(" ";A1)-1)

    Met de DEEL functie: tweede deel:

    =DEEL(A1;VIND.ALLES(" ";A1)+1;300)

    Een toepassing is het splitsen van namen in voornaam en achternaam.

    Voor- en achternaam omdraaien, als ze gescheiden zijn door een spatie en als er geen tussenvoegsels zijn:

    =RECHTS(A1;LENGTE(A1)-VIND.ALLES(" ";A1))&" "&LINKS(A1;VIND.ALLES(" ";A1)-1)

    Wil je via formules het volgende doen: de inhoud van een cel splitsen in 2 cellen, met een maximum aantal karakters voor beide delen, en tevens enkel splitsen op spaties (geen woorden afbreken). Zie dan op mijn pagina hier.

    Tekstgetallen

    Een 'tekstgetal' (ogenschijnlijk is het een getal, maar het is toch tekst) omzetten naar een getal - methode 1:

    =WAARDE(A1)

    Een 'tekstgetal' omzetten naar een getal - methode 2: =A1+0

    Een 'tekstgetal' omzetten naar een getal - methode 3: =A1*1

    Aantallen tellen

    Noot: de formules aangemerkt met een A mogen niet afgesloten worden met Enter, maar met Control + Shift + Enter. Het zijn matrixformules. Als je ze goed ingevoerd hebt, zet Excel automatisch een accolade aan begin en einde van de formule.

    Aantal T's in een cel? =LENGTE(A1)-LENGTE(SUBSTITUEREN(A1;"T";""))

    Aantal t's of T's in een cel - methode 1:

    =LENGTE(A1)-LENGTE(SUBSTITUEREN(HOOFDLETTERS(A1);"T";""))

    Aantal t's of T's in een cel - methode 2:

    =LENGTE(A1)-LENGTE(SUBSTITUEREN(KLEINE.LETTERS(A1);"t";""))

    Aantal klinkers in een cel: A

    =AANTAL(VIND.SPEC(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1);"aeiouy"))

    Aantal medeklinkers in een cel: A

    =AANTAL(VIND.SPEC(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1);"bcdfghjklmnpqrstvwxz"))

    Eenvoudiger zou zijn om het aantal karakters te tellen in de cel, en het aantal klinkers ervan af te halen. Maar dat houdt dan geen rekening met getallen of (lees)tekens.

    Aantal letters in een cel: A

    =AANTAL(VERGELIJKEN(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1);TEKEN(RIJ(INDIRECT("65:90")));0))

    Aantal cijfers in een cel - methode 1: A

    =AANTAL(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1)+0)

    Aantal cijfers in een cel - methode 2: A

    =AANTAL(VERGELIJKEN(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1);TEKEN(RIJ(INDIRECT("48:57")));0))

    Aantal (lees)tekens in een cel: A

    =AANTAL(VIND.ALLES(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1);",;:?!.*/+-^´`&(){}"))

    Veiliger zou zijn om het aantal cijfers en letters in de cel af te trekken van het aantal karakters.

    Aantal hoofdletters in een cel - methode 1: A

    =SOM(--NIET(GELIJK(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1);KLEINE.LETTERS(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1)))))

    Aantal kleine letters in een cel - methode 1: A

    =SOM(--NIET(GELIJK(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1);HOOFDLETTERS(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1)))))

    Aantal hoofdletters in een cel - methode 2:

    =SOMPRODUCT((CODE(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1))>=65)*(CODE(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1))<=90))

    Aantal kleine letters in een cel - methode 2:

    =SOMPRODUCT((CODE(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1))>=97)*(CODE(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1))<=122))

    Als je liever wat eigen gemaakte functies gebruikt, zie de bestandjes bovenaan de pagina.

    Andere

    Als een cel meerdere regels bevat ( Alt + Enter), staat de eerste Enter op:

    =VIND.ALLES(TEKEN(10);A1)

    Als A1 een datum bevat, zet je die in een andere opmaak met bv.:

    =TEKST(A1;"dddd, d/m/jjjj")

    Volledige pad, bestandsnaam en bladnaam: =CEL("bestandsnaam")

    Stel dat je vorige formule in B1 zet.

    De naam van het huidige blad geven - methode 1:

    =DEEL(B1;VIND.ALLES("]";B1)+1;300)

    De naam van het huidige blad geven - methode 2:

    =RECHTS(B1;LENGTE(B1)-VIND.ALLES("]";B1))

    De naam van het huidige bestand geven:

    =DEEL(B1;VIND.ALLES("[";B1)+1;VIND.ALLES("]";B1)-VIND.ALLES("[";B1)-1)

    De naam van het huidige pad geven - methode 1:

    =DEEL(B1;1;VIND.ALLES("[";B1)-1)

    De naam van het huidige pad geven - methode 2:

    =LINKS(B1;VIND.ALLES("[";B1)-1)

    De naam van het huidige pad geven - methode 3: =INFO("directory")

    Foutopsporing: stel dat je tekst hebt in cellen A1 en A2, en je wilt de plaats kennen van het het eerste verschil tussen beide teksten: A

    =VERGELIJKEN(ONWAAR;DEEL(A1;RIJ(INDIRECT("1:"&MIN(LENGTE(A1);LENGTE(A2))));1)=DEEL(A2;RIJ(INDIRECT("1:"&MIN(LENGTE(A1);LENGTE(A2))));1);0)

    Foutopsporing: simpeler, maar ten koste van enkele kleinigheden: A

    =VERGELIJKEN(ONWAAR;DEEL(A1;RIJ($1:$1024);1)=DEEL(A2;RIJ($1:$1024);1);0)

    Om tekst als product1;product2;product3;product6 om te zetten in product1, product2, product3 en product6 of product2;product3 om te zetten in product2 en product3:

    =SUBSTITUEREN(SUBSTITUEREN(A1;";";", ");", ";" en ";MAX(1;LENGTE(SUBSTITUEREN(A1;";";"^"))-LENGTE(SUBSTITUEREN(A1;";";""))))

    Laatste woorden over dit onderwerp

    Opgepast! In een aantal functies hierboven zal je nog moeten testen of aan bepaalde voorwaarden voldaan is. Anders krijg je foutmeldingen in de cel. Bv. als je zoekt op de vierde T in een cel, moet je eerst testen of er wel 4 T’s zijn:

    =LENGTE(A1)-LENGTE(SUBSTITUEREN(A1;"T";""))=4

    Excel geeft je WAAR als het er precies 4 zijn, ONWAAR als dit niet zo is. Zie ook de formule hierboven over het optellen van t’s en T’s.

    Jullie hebben nu de smaak te pakken! Probeer echter niet met ENKEL formules om tekst van een cel om te draaien: onmogelijk! Dit kan uiteraard wel met VBA code, maar dat is niet de bedoeling. Net zoals alles overige formules op deze pagina via VBA kunnen gemaakt worden.

    Voilà, dit was het. Je bent erdoor geworsteld, proficiat!




    Over Wim

    Wim Gielis is Business Intelligence consultant en Excel expert

    Andere links