Microsoft Excel: Count the words in a cell

By J. Carlton Collins, CPA

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."

techqa-3

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.

SPONSORED REPORT

Get your clients ready for tax season

Upon its enactment in March, the American Rescue Plan Act (ARPA) introduced many new tax changes, some of which retroactively affected 2020 returns. Making the right moves now can help you mitigate any surprises heading into 2022.

100th ANNIVERSARY

Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.