How to Count Words in Excel?

• To count the words in one cell of Excel, use the formula LEN, TRIM, and SUBSTITUTE to calculate the number of spaces between words and then add 1.
• In the formula I suggest using TRIM as this will also strip out any extra spaces and make word count more accurate.
• If the formula applies to multiple rows, you can copy it down a helper column to count words for each cell.
• To total words in a range, one of the following options works: use SUMPRODUCT or create a helper column and then use SUM.
• If you are using Microsoft 365, consider TEXTSPLIT for smaller and/or more frequent word count jobs, and Power Query for larger and/or more complex word count jobs.

Use this formula to count the words in a cell:

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)

This formula will count the number of words in A2.

I find this formula works well in most versions of Excel and is more robust when dealing with blank cells than a simple formula.

How the Formula Works

The formula will be based on the difference in length of the text with and without spaces.

Here is the logic:

LEN(TRIM(A2))

This will return a number representing the number of characters that the cell contains, excluding extra spaces.

SUBSTITUTE(TRIM(A2)," ","")

This deletes all spaces from the text.

LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1

This will count the number of spaces and then add 1. Typically, words will be separated by spaces, so the number of words will be equal to the number of spaces plus one.

The IF part prevents returning 1 if the cell is empty.

I like this technique because it is fairly simple and uses Excel logic: count the spaces between words and then add 1.

Example

Assume that A2is the cell containing:

Using Excel is easier when it comes to data analysis.

Use:

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)

The result will be:

5

I tested this example, and the formula returns 5 because the cell has five words.

To count words in one cell, enter this formula next to your text:

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)

Then press Enter.

This is the most common way to count words in Excel. I tried this example and it returned 5 as there are five words on the cell.

Enter the formula in B2 if you have text in column A and you want to count the number of words in each row:

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)

This is the way to go if you want to have a word count for each row.

Then, copy the formula to the bottom.

Example:

Useful for when every row has individual text, comments, descriptions, responses, etc.

To count how many words are in a range, like A2:A10 use:

=SUMPRODUCT(IF(LEN(TRIM(A2:A10))=0,0,LEN(TRIM(A2:A10))-LEN(SUBSTITUTE(TRIM(A2:A10)," ",""))+1))

This formula is directly usable in the newest versions of MS Excel or newer versions of MS 365.

If you are using an older version of Excel, you might need to type it in as an array formula:

Ctrl + Shift + Enter

Rather than Enter.

When I want one word count from multiple cells, I use this formula.

An easier way is a helper column.

In B2, enter:

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)

Record it on the bottom line for all the rows.

Then use:

=SUM(B2:B10)

This approach is easier to examine and troubleshoot, particularly in the case of large spreadsheets.

In some cases, it is desired to find out how many times the word occurs in a cell.

For instance, if the word Excel occurs in a cell A2, then count the number of times it occurs is:

=(LEN(A2)-LEN(SUBSTITUTE(A2,"Excel","")))/LEN("Excel")

This formula is sensitive to upper and lower case.

That means it counts Excel but not excel.

If you want to count a word, no matter if it is written in capital letters or lowercase letters, use:

=(LEN(LOWER(A2))-LEN(SUBSTITUTE(LOWER(A2),"excel","")))/LEN("excel")

This counts:

• Excel
• excel
• EXCEL

as the same word.

To count the number of times a word occurs in a range:

=SUMPRODUCT((LEN(LOWER(A2:A10))-LEN(SUBSTITUTE(LOWER(A2:A10),"excel","")))/LEN("excel"))

This will sum the value of the word excel in cells A2:A10.

The word count formula may be interrupted by extra spaces. For example:

Count   words   in   Excel

The TRIM function corrects for this by trimming any extra spaces between words.

For this reason, this is the formula that is recommended:

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)

If not, the outcome may be wrong, if TRIM is not used.

The basic formula does not necessarily give the correct word count when there are line breaks in cells. Line breaks can be removed by replacing them with spaces.

Use this formula:

=IF(LEN(TRIM(SUBSTITUTE(A2,CHAR(10)," ")))=0,0,LEN(TRIM(SUBSTITUTE(A2,CHAR(10)," ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A2,CHAR(10)," "))," ",""))+1)

Useful when copying text from web pages, forms, emails or documents.I use this version when I copy text from a website, form, email or a document.

If you use Microsoft 365, you can count words with TEXTSPLIT:

=COUNTA(TEXTSPLIT(TRIM(A2)," "))

It's more concise and easier to read.

If the cell can be blank, however, use:

=IF(TRIM(A2)="",0,COUNTA(TEXTSPLIT(TRIM(A2)," ")))

This approach works best for the newer versions of Excel.The LEN, TRIM, and SUBSTITUTE formula is the safest formula to use in most of the word count steps in Excel.

When you have to count words in a large data set, Power Query can be helpful.

To count the number of words in a Power Query:

1. Select your data.
2. Go to the Data tab.
3. Click From Table/Range.
4. Open data using Power Query Editor.
5. Add a "Custom Column".
6. Break a text up into words using a formula.
7. Count the items after they are made.
8. Import the data into Excel.

Power Query is also recommended for repetitive reporting as data can be refreshed when it changes.

For most users, this is the best Excel word count formula:

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)

It's reliable, works with most versions of Excel, works with blanks, and decreases errors from excess spaces.

The recommended formula works with blank cells because it uses IF to return 0.

=(LEN(A2)-LEN(SUBSTITUTE(A2,"word","")))/LEN("word")

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)