Count Number of Words in a Cell

0
135
Trim Function in Excel

Number of Words

This example describes how to count the number of words in a cell.

1a. The TRIM function returns a string with leading spaces, extra spaces and trailing spaces removed.

Trim Function in Excel

1b. To get the length of the string with normal spaces, we combine the LEN and TRIM function.

 

Len Function in Excel

2a. The SUBSTITUTE function replaces existing text with new text in a text string. We use the SUBSTITUTE function to get the string without spaces.

2b. To get the length of the string without spaces, we combine the LEN and SUBSTITUTE function.

3. Now comes the simple trick. To get the number of words, we subtract the length of the string without spaces (10) from the length of the string with normal spaces (12) and add 1.

Hope this helps! Leave us a comment if this post helped you or if you have any questions.