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).
Count the Number of Words in Google Sheets
Suppose you have a few text strings as shown below:
There are different text strings here of varying lengths.
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 is two. Similarly, ‘Have a Good Day’ has three spaces and four words.
Here is the formula that will give us the word count in Google Sheets:
How does this work?
- The LEN(A1) part of the function would give you the total number of characters in the text string (including the spaces).
- The LEN(SUBSTITUTE(A1,” “,“”)) part first removes all the spaces by using the SUBSTITUTE function. It then counts the length of the characters in the text string.
- The value 1 is added to the difference of these two formulas to get the total number of words.
But what if there is more than one space character between words?
In that case, this formula would give you the wrong result.
Counting the Number of Words (with more than one space in between words)
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.
The 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.
So this is how you can easily count words in Google Sheets using simple TEXT formulas.
I hope you found this tutorial useful!
You may also find the following tutorials useful:
- How to Count the Number of Words in Excel.
- How to Count Cells If Not Blank in Google Sheets.
- Using COUNTIF Function in Google Sheets.
- How to Remove Duplicates in Google Sheets.
- How to Create a Drop Down List in Google Sheets.
- 101 Google Sheets Keyboard Shortcuts.
- Capitalize First Letters in Google Sheets (using Formula)