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.
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))+1
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 cell of any erroneous spaces put in by user error, or error on import.
I have some problem arise to use this formula
Winter holiday your answer is 2, I got 1
Formula is
=len(trim(A4))-Len(substitute(trim(A4),” “,” “))+1
please help
Mathew
You do not need a space in the third argument of the LEN function. The formula has been revised below;
=len(trim(A4))-Len(substitute(trim(A4),” “,”“))+1
Hi!
Is this the same formula for Google Sheets? I’m trying to applicate it in, but it doesn’t work.
Do you any alternative?
Hi Roc,
Just tested it in Google Sheets and it worked fine. Double check the formula you are using. Are the cell references correct?
Alan