How to Count the Number of Spaces or any Character in a text

Friends, 

In this article, we will learn how to count the spaces or any special character or alphabet in a text in any cell in Microsoft Excel.



How to count the number of Characters in Excel?

LEN function is the answer. But LEN function alone will not help you to count how many spaces are there in a text string or in a sentence.

There is no specific Function in Microsoft Excel which can do this task. Therefore, we must use some trick to develop an Excel 
Formula which will do it for us. So, let’s do it. 

Here we will use two nested function to do the job, one LEN function to count the number of characters in a text and second SUBSTITUTE function which is used to replace specific text in a text, I have discussed in my last article.

How to Count the Number of Spaces or Alphabet or any Character in a text?

As we already know that SUBSTITUTE function replaces specific character and then it displays the modified text. For example, suppose replace the spaces by nothing (only opening and closing double quotes) between the words in the text “The quick brown fox”, the resultant text will be “Thequickbrownfox”. The spaces will be removed.

Now if we count the number of characters in this text using LEN function and deduct this number from the old text number (i.e., including spaces), we will get the number of spaces.

In the below image, I have calculated the number of spaces in a text step by step.

How to Count the number of Spaces in any Text in a cell

Step 1 : Get the number of characters in the given text using LEN function.

Step 2 : Replace the spaces using SUBSTITUTE function.

Step 3 : Count the number of characters in the text which is the output of SUBSTITUTE function.

Step 4 :  Now deduct the numbers and the difference is the number of spaces in that text.

The excel formula to count spaces or any other character or alphabet is =LEN(B3)-LEN(SUBSTITUTE(B3," ",""))

How to Count number of words in a text in excel?

Using the above excel formula we can also count the number of words in a text string. This is a tricky part, as we know in a sentence there are spaces between words, therefore, if we count the number of spaces and then add one with this number will be the number of words in a text (provided no double spaces are entered in the text).

So, the formula will be =LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1

You can further modify the formula by using TRIM function to remove the spaces before and after the text.

Now the formula will be =TRIM(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1)


Hope you have enjoyed this article "how to count the number of spaces in a text in Excel".
Thank you for reading…