Count total amount of words in a range
This tutorial shows how to return the total count of words from a range using an Excel formula, with the SUMPRODUCT, LEN, TRIM and SUBSTITUTE functions
|
=SUMPRODUCT(LEN(TRIM(B5:B8))-LEN(SUBSTITUTE(B5:B8," ",""))+1)
|
GENERIC FORMULA
=SUMPRODUCT(LEN(TRIM(range))-LEN(SUBSTITUTE(range," ",""))+1)
ARGUMENTS EXPLANATION For each cell in the range, the TRIM function removes spaces at the start and end of the text and also removes the unnecessary spaces between words, leaving only a single space between words. The trimmed string is then captured in the LEN function to return the number of characters of that string, including spaces. The SUBSTITUTE function is used to remove all spaces from a each cell in a range, which is then captured in the LEN function to to return the number of characters of that string. The formula then subtracts the number of characters in a string, excluding spaces, from the number of characters in a string, including spaces. It then adds 1 to account for the last word in a string.
The result of the above calculation is a word count list for each cell in the range. The SUMPRODUCT function is then used to sum this list of word counts that was calculated for each cell in the range and return a total count of words in the range.
In this example the range from which we are counting the total number of words is B5:B8, which contains a total of 14 words. |
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count total number of words in a string | How to count the total number of words in a string | |
| Return first word from a string | How to return the first word from a string |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| TRIM Function | The Excel TRIM function removes the spaces at the start and end of the text and also removes the unnecessary spaces between words, leaving only a single space between words and numbers that form part of the text | |
| SUMPRODUCT Function | The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values | |
| SUBSTITUTE Function | The Excel SUBSTITUTE function replaces characters with another in a specified string | |
| LEN Function | The Excel LEN function returns the number of characters in a specified string |