Count total number of words in a string
This tutorial shows how to count the total number of words in a string using an Excel formula, with the TRIM, SUBSTITUTE and LEN functions
Hard coded formula
Cell reference formula
|
=LEN(TRIM("Bread Milk Apples Cream"))-LEN(SUBSTITUTE("Bread Milk Apples Cream"," ",""))+1
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1
|
GENERIC FORMULA
=LEN(TRIM(string))-LEN(SUBSTITUTE(string," ",""))+1
ARGUMENTS GENERIC FORMULA
=LEN(TRIM(string))-LEN(SUBSTITUTE(string," ",""))+1
ARGUMENTS EXPLANATION This formula uses uses the TRIM function to remove 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 string 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.
Click on either the Hard Coded or Cell Reference button to view the formula that has string directly entered into the formula or referenced to specific cells. |
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return nth character | How to extract the nth character from a string | |
| Return first word from a string | How to return the first word from a string | |
| Return nth value from a string | How to get the nth value 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 | |
| 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 |