Generieke formule
=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)
Samenvatting
Als u moet tellen hoe vaak een specifiek woord (of een subtekenreeks) in een cel voorkomt, kunt u een formule gebruiken die SUBSTITUTE en LEN gebruikt. In de generieke vorm van de bovenstaande formule staat "tekst" voor een cel die tekst bevat en staat "woord" voor het woord of de subtekenreeks die wordt geteld. In het voorbeeld gebruiken we deze formule:
=(LEN(B4)-LEN(SUBSTITUTE(B4,C4,"")))/LEN(C4)
Uitleg
B4 is de cel waarin we woorden tellen, en C4 bevat de substring (woord of een substring) die je aan het tellen bent.
SUBSTITUTE verwijdert de subtekenreeks uit de originele tekst en LEN berekent de lengte van de tekst zonder de subtekenreeks. Dit aantal wordt vervolgens afgetrokken van de lengte van de originele tekst. Het resultaat is het aantal tekens dat is verwijderd door SUBSTITUTE.
Ten slotte wordt het aantal verwijderde tekens gedeeld door de lengte van de subtekenreeks. Dus als een subtekenreeks 5 tekens lang is en er ontbreken 10 tekens nadat deze uit de originele tekst is verwijderd, weten we dat de subtekenreeks twee keer in de originele tekst is verschenen.
Afhandelingsgeval
SUBSTITUTE is een hoofdlettergevoelige functie, dus het komt overeen met hoofdlettergebruik bij het uitvoeren van een vervanging. Als u zowel hoofdletters als kleine letters van een woord of substring moet tellen, gebruikt u de functie HOOFDLETTERS in SUBSTITUTE om de tekst naar hoofdletters te converteren voordat u de vervanging uitvoert:
=(LEN(B4)-LEN(SUBSTITUTE(UPPER(B4),UPPER(C4),"")))/LEN(C4)
Omdat deze formule de substring en de tekst omzet in hoofdletters voordat de vervanging wordt uitgevoerd, werkt deze formule in elk geval even goed met tekst.
Omgaan met problemen
Woorden tellen in Excel is lastig omdat Excel geen reguliere expressies ondersteunt. Het resultaat is dat het moeilijk is om de woorden die u wilt tellen exact te targeten, terwijl subtekenreeksen en andere gedeeltelijke overeenkomsten worden genegeerd (dwz zoek "vos" maar niet "vossen"). Interpunctie en hoofdlettervariaties maken dit probleem behoorlijk uitdagend.
Een oplossing is om als eerste stap een andere formule in een hulpkolom te gebruiken om "tekst te normaliseren". Gebruik vervolgens de formule op deze pagina om woorden te tellen die in spaties zijn gewikkeld om een nauwkeurige telling te krijgen (u kunt bijvoorbeeld zoeken naar "vos" in de genormaliseerde tekst.
Opmerking: deze benadering is zo goed als de genormaliseerde tekst die u kunt maken, en het kan zijn dat u de normaliseringsformule vaak moet aanpassen om het gewenste resultaat te krijgen.
