Count total words in a cell
Generic Formula:-
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1
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, and 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: