Tekens in een cel (of celbereik) tellen met formules in Excel

Excel heeft een aantal fantastische tekstfuncties die u kunnen helpen bij het werken met tekstgegevens.

In in sommige gevallen moet u 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, kunt u de rest ook doen met een combinatie van formules (zoals we later in de voorbeelden zullen zien).

In deze tutorial zal ik verschillende voorbeelden behandelen waarin u het totaal kunt tellen of specifieke tekens in een cel / bereik in Excel.

Tel alle tekens in een cel

Als je gewoon een totaal aantal tekens in een cel wilt hebben, kun je de LEN-functie gebruiken.

De LEN-functie heeft één argument nodig, wat de tekst in dubbele -quotes of de celverwijzing naar een cel die de tekst bevat.

Stel dat u de gegevensset heeft zoals hieronder weergegeven en u wilt het totale aantal tekens in elke cel tellen:

Hieronder staat de formule die dit zal doen:

=LEN(A2)

Door zelf ziet de LEN-functie er misschien niet zo 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 gaat om een speciaal teken, cijfers, leestekens en spaties (voorloop-, volg- en dubbele spaties tussen woorden).

Sinds de LEN functie telt elk teken in een cel, soms krijg je het verkeerde resultaat als je extra spaties in de cel hebt.

In het onderstaande geval geeft de LEN-functie bijvoorbeeld 25 terug voor de tekst in cel A1, terwijl het 22 had moeten zijn. Maar aangezien het ook extra spaties telt, jij krijg het verkeerde resultaat.

Om te voorkomen dat er extra spaties worden geteld, kunt u eerst de TRIM-functie gebruiken om eventuele leidende, volgspaties en dubbele spaties en gebruik vervolgens de LEN-functie erop om het echte aantal woorden te krijgen.

De onderstaande formule doet dit:

=LEN(TRIM(A2))

Tel alle tekens in een celbereik

Je kunt ook de LEN-functie gebruiken om het totale aantal tekens te tellen in een heel bereik.

Stel dat we dezelfde dataset hebben en deze keer wil ik, in plaats van het aantal tekens in elke cel, weten hoeveel er in het hele bereik zijn.

U kunt dat doen met behulp van de onderstaande formule:

=SUMPRODUCT(LEN(A2:A7)))

Laat me uitleggen hoe deze formule werkt.

In de bovenstaande formule neemt het LEN-deel van de functie een hele reeks cellen en telt het de tekens in elke cel.

De resultaat van de LEN fu nctie zou zijn:

{22; 21; 23; 23; 23; 31}

Elk van deze cijfers staat voor het aantal tekens in de cel.

En als je de SUMPRODUCT-functie ermee gebruikt, voegt het gewoon al deze getallen toe.

Als je je nu afvraagt waarom je SUM niet kunt gebruiken in plaats van SUMPRODUCT, dan is de reden dat dit een array, en SUMPRODUCT kan array aan, maar de SUM-functie niet.

Als je echter nog steeds SUM wilt gebruiken, kun je de onderstaande formule gebruiken (maar onthoud dat je Control + Shift + Enter om het resultaat te krijgen in plaats van een normale Enter)

=SUM(LEN(A2:A7))

Tel specifieke tekens in een cel

Zoals ik al zei, het echte hulpprogramma van de LEN-functie wordt gebruikt in combinatie met andere formules.

En als u specifieke tekens in een cel wilt tellen (dit kan een letter, cijfer, speciaal teken of spatie zijn), u kunt dat doen met een formulecombinatie.

Stel dat u de dataset heeft zoals weergegeven hieronder en u wilt het totale aantal woorden in elke cel tellen.

Hoewel er geen ingebouwde formule is om het aantal woorden te krijgen, kunt u de spatie-tekens tellen en deze gebruiken om het totaal te weten aantal woorden in de cel.

Hieronder staat de formule die u het totale aantal spaties in een cel geeft:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

De bovenstaande formule telt het totale aantal spaties en voegt vervolgens 1 toe aan dat aantal om het aantal woorden te krijgen.

Hier is hoe deze formule werkt:

• SUBSTITUTE-functie wordt gebruikt om alle spatie-tekens te vervangen door een spatie. De LEN-functie wordt vervolgens gebruikt om het totale aantal tekens te tellen als er geen spatie-tekens zijn.
• Het resultaat van de LEN (SUBSTITUTE (A2, “”, “”)) wordt vervolgens afgetrokken van LEN ( A2) Dit geeft ons het totale aantal spaties in de cel.
• 1 wordt toegevoegd in de formule en het totale aantal woorden zou één meer zijn dan het totale aantal spaties (aangezien twee woorden worden gescheiden door één teken).

Merk op dat als er voorloop-, volg- of dubbele spaties zijn, u het verkeerde resultaat krijgt. In dat geval is het het beste om de TRIM-functie samen met de LEN-functie te gebruiken.

U kunt dezelfde logica ook gebruiken om een specifiek teken of woord of zin in een cel te vinden.

Stel dat ik bijvoorbeeld een dataset heb zoals hieronder getoond, waar ik verschillende batches heb, waarbij elke batch een alfabet en nummer heeft om het weer te geven (zoals A1, J2, enz.)

Hieronder staat de formule die u het totaal aantal keren geeft dat een batch met het alfabet A elke maand is aangemaakt:

=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))

De bovenstaande formule gebruikt opnieuw dezelfde logica – zoek de lengte van de tekst in een cel met en zonder het teken dat je wilt tellen en neem dan het verschil van deze twee.

In de bovenstaande formule heb ik het teken dat ik wil tellen hard gecodeerd, maar je kunt het ook in een cel plaatsen en dan gebruik de celverwijzing. Dit maakt het handiger omdat de formule wordt bijgewerkt de volgende keer dat u de tekst in de cel wijzigt.

Tel specifieke tekens met behulp van niet-hoofdlettergevoelige formule

Er is een probleem met de formule wordt gebruikt om specifieke tekens in een cel te tellen.

De functie SUBSTITUTE is hoofdlettergevoelig. Dit betekent dat u “A” niet gelijk is aan “a”. Dit is de reden waarom u het verkeerde resultaat krijgt in cel C5 (het resultaat had 3 moeten zijn).

Dus hoe kunt u verkrijg het aantal tekens van een specifiek teken wanneer het in elk geval had kunnen zijn (onder of boven).

U doet dat door uw formule hoofdletterongevoelig te maken. Hoewel je voor een complexe formule kunt gaan, heb ik gewoon het aantal tekens van beide hoofdletters toegevoegd (kleine letters en hoofdletters A).

=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))+LEN(B2)-LEN(SUBSTITUTE(B2,"a",""))

Tekens / cijfers tellen voor en na decimaal

Ik weet niet waarom, maar dit is een veel voorkomende vraag die ik krijg van mijn lezers en heb gezien in veel forums – zoals deze

Stel dat je een dataset hebt zoals hieronder getoond en je wilt de karakters tellen voor de komma en na de komma.

Hieronder staan de formules die dit zullen doen.

Tel tekens / cijfers vóór de komma:

=LEN(INT(A2))

Tel tekens / cijfers achter de komma:

=LEN(A2)-FIND(".",A2)

Merk op dat deze formules alleen bedoeld zijn voor significante cijfers in de cel. Als je voorloopnullen of volgnullen hebt of je hebt aangepaste getalnotatie gebruikt om meer / minder getallen weer te geven, dan geven de bovenstaande formules je nog steeds significante cijfers voor en na de komma.

Dit zijn dus enkele van de scenarios waarin u formules kunt gebruiken om tekens in een cel of een celbereik in Excel te tellen.

Ik hoop dat u de tutorial nuttig vond!

Andere Excel-tutorials die u misschien leuk vindt:

• Unieke waarden tellen in Excel met de AANTAL.ALS-functie
• Cellen tellen die tekstreeksen bevatten
• Gekleurde cellen tellen in Excel
• Unieke waarden tellen in Excel met de AANTAL.ALS-functie
• Tekst voor of na een specifiek teken in Excel verwijderen