How to Count Words in Excel

Counting the number of words in a block of text can be a useful task in Excel, especially when working with large amounts of text. In this blog post, we will learn how to count words in Excel using the built-in function, LEN and SUBSTITUTE.

Guide How to Count Words in Excel

Enter your text into a cell or range of cells that you want to count the words of.

In the cell next to it, enter the following Excel formula:

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

Replace “A1” with the cell reference that contains your text.

Press Enter, and the result will show the number of words in the text.

What is happening with the function?

The LEN function is used to count the total number of characters in the text, including spaces. The SUBSTITUTE function is used to remove all spaces from the text.

The difference between the total number of characters and the number of characters without spaces gives the number of spaces in the text.

Adding 1 to the number of spaces gives the number of words in the text.

Things to keep in mind:

This formula counts only the number of words in one cell or range of cells.
It assumes that each word is separated by a space.
If there are other delimiters, such as commas or semicolons, the formula needs to be modified accordingly.
It does not take into account any formatting, such as bold or italicized text, that may affect the word count.

How to Count Words in Excel Example

Suppose you have a cell containing text in cell A1, and you want to count the number of words in that text. You can use the following formula:

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

This formula calculates the length of the text string, subtracts the length of the text string with all spaces removed, and then adds 1 to get the word count.

Here’s a breakdown of the formula:

“LEN(A1)” returns the length of the text string in cell A1.
“SUBSTITUTE(A1,” “,””)” replaces all spaces in the text string with empty strings, effectively removing all spaces from the string.
“LEN(SUBSTITUTE(A1,” “,””))” returns the length of the text string with all spaces removed.
“LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))” subtracts the length of the text string with all spaces removed from the length of the original text string, giving us the total number of spaces in the text.
“LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))+1″ adds 1 to the total number of spaces to get the total number of words in the text.

Note that this formula counts spaces as word separators, so it may not work well for languages that don’t use spaces between words. Additionally, if your text contains punctuation or other special characters, you may need to modify the formula to account for them.

Count Words in Excel that Contains Punctuation

If you want to count words in text that contains punctuation or other special characters, you can modify the formula to remove those characters before counting the words.

Here’s an example formula that counts the number of words in a text string while ignoring any punctuation or special characters:

=(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),"!","")," ",""))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""), "!",""), " ", ""))+1

Let’s break this down step by step:

SUBSTITUTE(A1,”.”,””) removes all periods from the text in cell A1.
SUBSTITUTE(result of step 1,”!”,””) removes all exclamation marks from the text after the periods have been removed.
SUBSTITUTE(result of step 2,” “,””) removes all spaces from the text after the periods and exclamation marks have been removed.
LEN(result of step 3) calculates the length of the modified text string with all punctuation and special characters removed.
SUBSTITUTE(A1,”.”,””) removes all periods from the original text in cell A1.
SUBSTITUTE(result of step 5,”!”,””) removes all exclamation marks from the original text after the periods have been removed.
SUBSTITUTE(result of step 6,” “,””) removes all spaces from the original text after the periods and exclamation marks have been removed.
LEN(result of step 7) calculates the length of the original text string with all punctuation and special characters removed.
LEN(result of step 3)-LEN(result of step 8) calculates the difference between the length of the modified text string with all punctuation and special characters removed, and the length of the original text string with all punctuation and special characters removed.
Result of step 9+1 adds 1 to the difference calculated in step 9 to account for the first word in the text.

So the final result is the number of words in the text, even if it contains punctuation or other special characters.

It’s worth noting that this formula is not perfect, and may not work well in all cases.

For example, if the text contains words with hyphens or apostrophes, those characters may be treated as word separators and result in an incorrect word count.

In such cases, you may need to modify the formula to account for those characters.