You can easily count the number of words in a text string in Google Sheets. All you need is some know-how of the Text Functions (LEN and SUBSTITUTE).
Suppose you have a few text strings as shown below:
There are different text strings here of varying length.
Now to count the number of words, instead of counting the words, we can count the number of spaces in between the words.
For example, in ‘Hello World’, there is one space, so we would know that the number of words are two. Similarly, in ‘Have a Good Day’ has three spaces and four words.
Here is the formula that will give us the word count:
How this Works?
But what if there are more than one space characters between words?
In that case, this formula would give you a wrong result.
Suppose you have a dataset as shown below:
You can see that the text is the same, but since the number of spaces between the words vary, the word count given the formula used above gives a different result.
There is a simple fix to this problem – the TRIM function.
TRIM function removes all the extra leading, trailing, and double spaces between words. Only a single space character between words is allowed.
Here the formula that will give you the right result:
All we have done is replaced the cell reference A1 with TRIM(A1). This makes sure that the text in the cell has been treated for leading, trailing, and double spaces between words.
You may also find the following tutorials useful: