Microsoft Excel: Count the words in a cell

Q. In Excel, is it possible to count the number of words in a cell?

A. Because there is a space character between each word, you can count the words simply by counting the number of spaces between those words, and then add 1 to account for the last word. For example, cell A2 below contains a phrase, and cell B2 counts the number of words in that phrase using the formula =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))+1.

Here’s how this formula works:

1. TRIM. The TRIM functions in this formula remove any consecutive duplicate spaces from the phrase, just in case the phrase happens to contain extra spaces between the words or ­additional spaces after the last word.

2. LEN. The first LEN function counts the total number of text characters in cell A2 — 45 ­characters in this example.

3. SUBSTITUTE and LEN. The SUBSTITUTE function removes all spaces from the phrase in cell A2, and the second LEN function then counts the total number of text characters again, this time without any spaces — 35 in this example.

4. Simple math. Subtracting the length of the phrase without spaces (35 in this example) from the length of the phrase with spaces (45 in this example) yields the total number of spaces (10 in this example). Next, adding 1 produces the total number of words in the phrase (11 words in this example).

Of course, this approach does not take into account hyphenated compound words, such as look-alike or mother-in-law; closed compound words, such as spaceship or backspace (which you may want to count as separate words); or open compound words that contain a space, such as au pair or ice cream, depending on your definition of “word.”

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.