wimgielis.com

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 Ctrl + 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!