Excel heeft een aantal geweldige tekstfuncties die u kunnen helpen bij het werken met de tekstgegevens.
In sommige gevallen moet u mogelijk het totale aantal tekens in een cel/bereik berekenen of het aantal keren dat een specifiek teken in een cel voorkomt.
Hoewel er de LEN-functie is die het aantal tekens in een cel kan tellen, kunt u de rest ook doen met een combinatie van formules (zoals we later in de voorbeelden zullen zien).
In deze zelfstudie behandel ik verschillende voorbeelden waarbij u totale of specifieke tekens in een cel/bereik in Excel kunt tellen.
Tel alle tekens in een cel
Als u gewoon een totaal aantal tekens in een cel wilt krijgen, kunt u de LEN-functie gebruiken.
De LEN-functie heeft één argument nodig, wat de tekst tussen dubbele aanhalingstekens kan zijn of de celverwijzing naar een cel met de tekst.
Stel dat u de onderstaande gegevensset hebt en dat u het totale aantal tekens in elke cel wilt tellen:
Hieronder is de formule die dit zal doen:
=LENGTE(A2)
Op zichzelf ziet de LEN-functie er misschien niet zo goed uit, maar als je het combineert met andere formules, kan het een aantal geweldige dingen doen (zoals het aantal woorden in een cel krijgen of de voor- en achternaam splitsen).
Opmerking: de LEN-functie telt alle tekens in een cel, of het nu een speciaal teken, cijfers, leestekens en spatietekens zijn (voorloop-, volg- en dubbele spaties tussen woorden).
Aangezien de LEN-functie elk teken in een cel telt, kan het gebeuren dat u het verkeerde resultaat krijgt als u extra spaties in de cel heeft.
In het onderstaande geval retourneert de LEN-functie bijvoorbeeld 25 voor de tekst in cel A1, terwijl het 22 had moeten zijn. Maar omdat het ook extra spatietekens telt, krijg je het verkeerde resultaat.
Om te voorkomen dat extra spaties worden geteld, kunt u eerst de TRIM-functie gebruiken om voorloop-, volg- en dubbele spaties te verwijderen en vervolgens de LEN-functie erop gebruiken om het echte aantal woorden te krijgen.
Onderstaande formule doet dit:
=LENGTE(TRIM(A2))
Tel alle tekens in een reeks cellen
U kunt ook de LEN-functie gebruiken om het totale aantal tekens in een volledig bereik te tellen.
Stel dat we dezelfde dataset hebben en deze keer, in plaats van het aantal tekens in elke cel te krijgen, wil ik weten hoeveel er in het hele bereik zijn.
U kunt dat doen met behulp van de onderstaande formule:
=SOMPRODUCT(LENGTE(A2:A7)))
Laat me uitleggen hoe deze formule werkt.
In de bovenstaande formule neemt het LEN-gedeelte van de functie een heel cellenbereik in beslag en telt het de tekens in elke cel.
Het resultaat van de LEN-functie zou zijn:
{22;21;23;23;23;31}
Elk van deze getallen staat voor het aantal tekens in de cel.
En als je de SOMPRODUCT-functie ermee gebruikt, zou het gewoon al deze getallen optellen.
Nu, als je je afvraagt waarom je SUM niet kunt gebruiken in plaats van SOMPRODUCT, de reden is dat dit een array is, en SOMPRODUCT kan array aan, maar de SOM-functie niet.
Als u echter nog steeds SUM wilt gebruiken, kunt u de onderstaande formule gebruiken (maar onthoud dat u Control + Shift + Enter moet gebruiken om het resultaat te krijgen in plaats van een gewone Enter)
=SOM(LENGTE(A2:A7))
Specifieke tekens in een cel tellen
Zoals ik al zei, is het echte nut van de LEN-functie wanneer deze wordt gebruikt in combinatie met andere formules.
En als u specifieke tekens in een cel wilt tellen (het kan een letter, cijfer, speciaal teken of spatieteken zijn), kunt u dat doen met een formulecombinatie.
Stel dat u de onderstaande gegevensset hebt en dat u het totale aantal woorden in elke cel wilt tellen.
Hoewel er geen ingebouwde formule is om het aantal woorden te krijgen, kunt u de spatietekens tellen en deze vervolgens gebruiken om het totale aantal woorden in de cel te weten.
Hieronder vindt u de formule die u het totale aantal spatietekens in een cel geeft:
=LENGTE(A2)-LENGTE(VERVANGING(A2," ",""))+1
De bovenstaande formule telt het totale aantal spatietekens en voegt vervolgens 1 toe aan dat aantal om het aantal woorden te krijgen.
Zo werkt deze formule:
- De SUBSTITUTE-functie wordt gebruikt om alle spatietekens te vervangen door een spatie. De LEN-functie wordt dan gebruikt om het totale aantal tekens te tellen als er geen spatietekens zijn.
- Het resultaat van de LEN(VERVANG(A2," ","")) wordt vervolgens afgetrokken van LEN(A2). Dit geeft ons het totale aantal spatietekens dat zich in de cel bevindt.
- 1 wordt toegevoegd in de formule en het totale aantal woorden zou één meer zijn dan het totale aantal spatietekens (omdat twee woorden worden gescheiden door één teken).
Houd er rekening mee dat als er voorloop-, volg- of dubbele spaties zijn, u het verkeerde resultaat krijgt. In een dergelijk geval kunt u het beste de TRIM-functie samen met de LEN-functie gebruiken.
U kunt dezelfde logica ook gebruiken om een specifiek teken, woord of zin in een cel te zoeken.
Stel dat ik een dataset heb zoals hieronder getoond, waar ik verschillende batches heb, waarbij elke batch een alfabet en nummer heeft om deze weer te geven (zoals A1, J2, etc.)
Hieronder vindt u de formule die u het totale aantal keren geeft dat een batch met het alfabet A elke maand is gemaakt:
=LENGTE(B2)-LENGTE(VERVANGING(B2,"A",""))
De bovenstaande formule gebruikt opnieuw dezelfde logica - zoek de lengte van de tekst in een cel met en zonder het teken dat u wilt tellen en neem dan het verschil van deze twee.
In de bovenstaande formule heb ik het teken dat ik wil tellen hardgecodeerd, maar je kunt het ook in een cel plaatsen en vervolgens de celverwijzing gebruiken. Dit maakt het handiger omdat de formule de volgende keer dat u de tekst in de cel wijzigt, wordt bijgewerkt.
Tel specifieke tekens met behulp van hoofdletterongevoelige formule
Er is één probleem met de formule die wordt gebruikt om specifieke tekens in een cel te tellen.
De SUBSTITUTE-functie is hoofdlettergevoelig. Dit betekent dat u "A" niet gelijk is aan "a". Dit is de reden waarom je het verkeerde resultaat krijgt in cel C5 (het resultaat had 3 moeten zijn).
Dus hoe kun je het aantal tekens van een specifiek teken krijgen als het in elk geval had kunnen zijn (lager of hoger).
Dat doe je door je formule hoofdletterongevoelig te maken. Hoewel je voor een complexe formule kunt gaan, heb ik eenvoudig het aantal tekens van beide hoofdletters (kleine letters en hoofdletters A) toegevoegd.
=LENGTE(B2)-LENGTE(VERVANGING(B2,"A",""))+LENGTE(B2)-LENGTE(VERVANGING(B2,"a",""))
Tekens/cijfers tellen voor en na decimaal
Ik weet niet waarom, maar dit is een veel voorkomende vraag die ik van mijn lezers krijg en op veel forums heb gezien - zoals deze
Stel je hebt een dataset zoals hieronder weergegeven en je wilt de tekens voor de komma en achter de komma tellen.
Hieronder staan de formules die dit zullen doen.
Tel tekens/cijfers voor de komma:
=LENGTE(INT(A2))
Tel tekens/cijfers achter de komma:
=LENGTE(A2)-VINDEN(".",A2)
Merk op dat deze formules alleen bedoeld zijn voor significante cijfers in de cel. Als u voorloop- of naloopnullen hebt of als u aangepaste getalopmaak hebt gebruikt om meer/minder getallen weer te geven, geven de bovenstaande formules u nog steeds significante cijfers voor en achter de komma.
Dit zijn dus enkele van de scenario's waarin u formules kunt gebruiken om tekens in een cel of een celbereik in Excel te tellen.
Ik hoop dat je de tutorial nuttig vond!
