In this post you will learn how to count the number of times a specific character occurs in a text string in Excel.

### Counting specific characters in a cell –

A useful trick is to be able to count the number of times a specific character exists in a text

string. The technique for doing this in Excel is relatively clever. If you wanted to count the

number of times the letter s appears in the word Mississippi, for example, you could count

them by hand, of course, but systematically, you could follow these general steps:

- Measure the character length of the word Mississippi (11 characters).
- Measure the character length after removing every letter s (7 characters).
- Subtract the adjusted length from the original length.

After performing these steps, you’ll accurately conclude that the letter s occurs four times

in the word Mississippi.

A real-world use for this technique of counting specific characters is to calculate a word

count in Excel. In Figure below, you’ll see the following formula used to count the number of

words entered in cell B4 (nine words in this case):

`=LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1`

The formula first uses the LEN function to measure the length of the text in cell B4:

`LEN(B4)`

It then uses the SUBSTITUTE function to remove the spaces from the text:

`SUBSTITUTE(B4," ","")`

Wrapping the SUBSTITUTE function in a LEN function gives us the length of the text

without the spaces. Note that we have to add one (+1) to that answer to account for the

fact that the last word will not have an associated space:

`LEN(SUBSTITUTE(B4," ",""))+1`

Subtracting the adjusted length from the original length gives us our word count:

`=LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1`