In this article, I will show you how to count the total number of words in a cell or range of cells Excel. And how to count the number of times a particular word appears in a cell or range of cells.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
How to count words in Excel
1. The TRIM function below removes extra spaces.
2. To get the length of this string, add the LEN function.
3. The SUBSTITUTE function below returns a string without spaces.
4. To get the length of this string, add the LEN function.
5. To count the total number of words in cell A1, subtract the length of the string without spaces (formula from step 4) with the length of the regular whitespace string (formula from step 2) and add 1.
Conclusion: to count the total number of words in a cell, you just need to count the number of spaces and add 1. 1 space means 2 words, 2 spaces means 3 words, etc.
6. To count the total number of words in the range A1:A2, add the SUMPRODUCT function and replace A1 with A1:A2.
Next I will show you how to count the number of repeated words in a cell.
7. The LEN function below returns the length of the original text in cell A1.
8. The SUBSTITUTE function below returns the string without the word “dog”.
9. The LEN function below returns the length of this string.
10. Subtract the length of the string without the word “dog” (formula from step 9) with the length of the original text in cell A1 (formula from step 7).
Conclusion: after removing the word “dog” from the original text, there are 12 characters left.
11. We know the length of the word “dog” (3), so the word “dog” appears 12/3 = 4 times in cell A1.
12. To count the number of times the word “dog” occurs in the range A1:A2, add the SUMPRODUCT function and replace A1 with A1:A2.
13. Use the COUNTIF function in Excel to count the number of cells containing a specific word.
Note: asterisk
The article achieved: 5/5 – (100 votes)
