Exceljet

Quick, clean, and to the point

Count total words in a cell

Excel formula: Count total words in a cell
Generic formula 
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1
Summary 

To count the total words in a cell, you can use a formula based on the LEN and SUBSTITUTE functions, with help from the TRIM function. In the example shown, the formula in cell C5, copied down, is:

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

The formula returns the word count in cell B5.

Explanation 

Excel doesn't have a dedicated function for counting words in a cell. However, with a little ingenuity, you can create such a formula using the SUBSTITUTE and LEN functions, with help from TRIM, as shown in the example. 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. This works, because word count is equal to the number of spaces + 1, so long as there is one space between each word.

The first part of the formula counts the characters in cell B5, after removing extra space:

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

Inside LEN, the TRIM function first 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. In this case, there are no extra space characters, so TRIM returns the original text directly to the LEN function, which returns 30:

LEN("All Quiet on the Western Front") // returns 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 look for a space character (" "), and replace with an empty string (""). By default, SUBSTITUTE will replace all spaces. The result is delivered directly 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:

=30-25+1 // returns 6

which returns 6 as a final result, the number of words in cell B5.

Dealing with blank cells

The formula in the example will return 1 even if a cell is empty, or contains only space. This happens because we are adding 1 unconditionally, after counting space characters between words. To guard against this problem, you can adapt the formula as shown below:

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

Notice we've replaced 1 with this expression:

LEN(TRIM(B5))>0

This code first trims B5, then checks the length. If B5 contains text, LEN returns a positive number, and the expression returns TRUE. If B5 is empty, or contains only space, TRIM returns an empty string ("") to LEN. In that case, LEN returns zero (0) and the expression returns FALSE. The trick is that TRUE and FALSE evaluate to 1 and zero, respectively, when involved in any math operation. As a result, the expression only adds 1 when there is text in B5. Otherwise, it adds zero (0). This logic could also be written with the IF function statement like this:

IF(LEN(TRIM(B5))>0,1,0)

and the result would be the same. The expression above is simply more compact.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.