Excel Examples: Counting Words From Any Cell

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