Working recently at a large Internet company they needed to find how many words were in a cell. This was because they had imported hundreds of thousands of keywords that customers had used to find their site through search engines.
To analyse this data they wanted to count how many words were in each cell containing keyword searches. Excel provides many text functions for managing and manipulating the text in the cells of your spreadsheet. The following formula did the job.
This formula subtracts a version of the cell without spaces between each word from a version still with spaces. This finds out how many spaces there are. There will be one less space than there are words so the resulting answer has a 1 added to it to find how many words there are.
The Len function is used to find how many text characters are in the cell.
The Substitute function is used to replace the spaces between each word with nothing making one long word.
The Trim function is used to remove any spaces at the beginning or end of the cell content. This cleanses the cells of any erroneous spaces put in by user error, or error on import.
Count the Number of Words in a Cell – Microsoft Excel Tips and Tricks