Excel doesn’t have a dedicated function for counting words.
In newer versions of Excel, the best approach is to use the TEXTSPLIT and COUNTA functions.
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.
The result from TEXTSPLIT is an array that contains the six words in the title cell B5.
This array is returned directly to theCOUNTA functionlike this:
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.
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.
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.
As a result, we need to remove empty valuesbeforethey are evaluated by COUNTA.
This is important since any extra spaces will throw off the word count.
By default, SUBSTITUTE will replaceallspaces.
However, this step can cause a problem with empty or blank cells, as explained below.
LEN will also count characters in numbers, but number formatting is not included.
SUBSTITUTE Function
The Excel SUBSTITUTE function replaces text in a given string by matching.
Related videos
How to clean text with CLEAN and TRIM
