If you need to count how many times a specific a word (or any substring) appears inside a cell, you can use a formula that uses SUBSTITUTE and LEN. In the generic form of the formula above, a1 represents a cell that contains text, and txt represents the word or substring being counted.
B4 is the cell we're counting words in, and C4 contains the substring (word or any substring) you are counting.
SUBSTITUTE removes the substring from the original text and LEN calculates the length of the text without the substring. This number is then subtracted from the length of the original text. The result is the number of characters that were removed by SUBSTITUTE.
Finally, the number of characters removed is divided by the length of the substring. So, if a substring is 5 characters long, and there are 10 characters missing after it's been removed from the original text, we know the substring appeared twice in the original text.
SUBSTITUTE is a case-sensitive function, so it will match case when running a substitution. If you need to count both upper and lower case occurrences of a word or substring, use the UPPER function inside SUBSTITUTE to convert the text to uppercase before running the substitution:
Because this formula converts the substring and the text to uppercase before performing the substitution, it will work equally well with text in any case.
Counting words in Excel is tricky because Excel doesn't support regular expressions. As a result, it's difficult to build matches that target the words you want to count exactly, while ignoring substrings and other partial matches (look for "fox " to avoid "foxes"). Punctuation and case variations make this problem quite challenging.
One workaround is to use another formula in a helper column to "normalize text" as a first step. Then use the formula on this page to count words wrapped in space characters to get an accurate count (i.e. you can look for " fox " in the normalized text.
Note: this approach is only as good as the normalized text you are able to create, and you might need to adjust the normalizing formula many times to get the result you need.
To count how many times a specific character appears in a cell, you can use a formula based on the SUBSTITUTE and LEN functions. In the generic form of the formula above, A1 represents the cell address, and "a" represents the character you want to...
To count how many times a specific a word (or any substring) appears inside a range of cells, you can use a formula based on the SUBSTITUTE, LEN, and SUMPRODUCT functions. In the example shown, the formula in C11 is: = SUMPRODUCT (( LEN ( B5:B8 ) -...
If you need to count specific characters in a range of cells, you can do so with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. In the generic form of the formula (above), rng represents a range of cells that contain...
To test a cell to see if it contains one of many strings, you can use a formula based on the SEARCH , ISNUMBER and SUMPRODUCT functions. The formula in C5, copied down, is: = SUMPRODUCT ( -- ISNUMBER ( SEARCH ( things , B5 ))) > 0 where things is...
The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.
The Excel UPPER function returns a upper-case version of a given text string. Numbers and punctuation are not affected.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.