Count total words in a cell

In this example, the goal is to count the total number of words in a cell. Excel doesn't have a dedicated function for counting words. However, with a little ingenuity, you can create a formula to perform this task using a combination of built-in functions. In newer versions of Excel, the best approach is to use the TEXTSPLIT and COUNTA functions. In older versions of Excel, you can use a more complicated formula based on the SUBSTITUTE and LEN functions. Both formulas use the TRIM function to clean up the source text before counting words, and both formulas are explained in detail below. This is a great example of how newer functions in Excel are simplifying older more complex formulas. While the older SUBSTITUTE option does work, it works indirectly and is not very intuitive. The TEXTSPLIT option is logical and straightforward; it just makes sense.

TEXTSPLIT option

In newer versions of Excel, the best approach is to use the TEXTSPLIT function in a formula like this:

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

At a high level, this formula works in three distinct and logical steps:

1. Clean up the text with TRIM (remove extra space)
2. Create a list of words with TEXTSPLIT
3. Count the words in the list with COUNTA

Working from the inside out, the TRIM function is used first to remove any extra spaces:

TRIM(B5) // remove extra spaces

TRIM will convert any runs of spaces into a single space, and remove any leading or trailing space characters. Next, TRIM returns the trimmed text directly to the TEXTSPLIT function as the text argument:

TEXTSPLIT("All Quiet on the Western Front"," ")

The delimiter in TEXTSPLIT is provided as a single space (" ") since words are separated by spaces. The result from TEXTSPLIT is an array that contains the six words in the title cell B5. This array is returned directly to the COUNTA function like this:

=COUNTA({"All","Quiet","on","the","Western","Front"})

The COUNTA function will count both numbers and text. Because the array from TEXTSPLIT contains six words, COUNTA returns a final result of 6. As the formula is copied down, it returns a word count for each title in column B.

Handling empty cells

If a cell is empty, or when a cell contains only space characters, TRIM will return an empty string (""). Unfortunately, the TEXTSPLIT function will return a #CALC! error if the source text is an empty text string and the COUNTA function will count the #CALC! error and return the incorrect result of 1. Consequently, we need a way to prevent the formula from returning 1 if blank or empty cells are a possibility. One way to handle this situation is to use a modified version of the formula like this:

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

Here, we first check for an empty cell (or a cell that contains only space) with the IF function and the TRIM function. If the result from TRIM is an empty string (""), IF simply returns 0 as a final result. Otherwise, IF runs the original formula which returns a word count as explained above.

Adjusting delimiters

The formula in the example above assumes that words are delimited by spaces only. If you are working with text words separated by other delimiters, you will need to adjust the delimiters provided to TEXTSPLIT as needed. For example, to count words that may be separated by spaces, commas, or hyphens, you can use a modified formula like this:

=COUNTA(TEXTSPLIT(TRIM(B5),{" ",",","-"},,1))

Here, we are providing the three delimiters as an array constant like this:

{" ",",","-"}

TEXTSPLIT will split the incoming text when it encounters any of these delimiters. In addition, we have set the ignore_empty argument in TEXTSPLIT to 1 (TRUE) to remove empty strings ("") that can be generated by consecutive delimiters that contain no content. We do this because COUNTA will (for some reason) count empty strings ("") as values. You would think that =COUNTA("") would return zero, but in fact, it returns 1. As a result, we need to remove empty values before they are evaluated by COUNTA.

Older versions of Excel

Older versions of Excel don't have the TEXTSPLIT function to work with, so they don't support the formula explained above. However, you can use a more complicated formula based on the LEN and SUBSTITUTE functions, with help from the TRIM function like this:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1

At a high level, this formula uses the LEN function to count the number of characters in the cell, with and without spaces, then uses the difference to figure out the word count:

1. Clean up the text with TRIM
2. Count characters in the result with LEN
3. Remove all space characters with SUBSTITUTE
4. Count characters in the result with LEN
5. Subtract the second count from the first count
6. Add 1 to get a final word count

This works because word count is equal to the number of spaces + 1, but it's a messy non-intuitive process.

The first part of the formula counts the characters in cell B5, after cleaning up spaces:

=LEN(TRIM(B5)) // normalize space, count characters

Inside LEN, the TRIM function removes any extra spaces between words, or at the beginning or end of the text. This is important since any extra spaces will throw off the word count. TRIM returns the trimmed text directly to the LEN function:

LEN("All Quiet on the Western Front") // returns 30

Since the text in cell B5 does not contain extra space characters, the text delivered by TRIM is unchanged and the result from LEN is 30. At this point, we have:

=30-LEN(SUBSTITUTE(B5," ",""))+1

Next, we use the SUBSTITUTE function to remove all space characters from the text:

SUBSTITUTE(B5," ","") // strip all space

Notice SUBSTITUTE is configured to replace a single space character (" ") with an empty string (""). By default, SUBSTITUTE will replace all spaces. The result is delivered to the LEN function, which returns the count:

LEN("AllQuietontheWesternFront") // returns 25

LEN returns 25, the number of characters remaining after all space has been removed. We can now simplify the formula to the following:

=30-25+1 // returns 6

The final result is 6 which is the number of words in cell B5. Notice we need to add 1 at the end because we are counting spaces between words, and there will always be one less space than there are words. However, this step can cause a problem with empty or blank cells, as explained below.

Dealing with blank cells

The code explained above will return 1 even if a cell is empty, or contains only space. This happens because we are adding 1 unconditionally to the result, which only makes sense if we have at least one word. To guard against this problem, you can adapt the formula as shown below:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(TRIM(B5)<>"")

Notice this is the original formula except that we've replaced +1 with an expression:

+(TRIM(B5)<>"")

This code first trims the text in cell B5, then it tests to see if the result is not empty. If TRIM returns actual text, the expression returns TRUE. If TRIM returns an empty string (""), because B5 is empty, or contains only space, the result is FALSE. The trick here is that Excel evaluates TRUE as 1 and FALSE as 0 when they are involved in any math operation. As a result, the expression adds 1 when there is text in B5 and adds 0 if the cell is empty or contains only space.