Contents
Task
Suppose we have some text in cell A1 and in cell B1 we need to count the number of words in that text.
Solution
A simple but beautiful solution for counting the number of words in a cell would be the following formula for B1:
=IF(ISBLANK(A1),0;DLSTR(TRIM(A1))—DLSTR(SUBSTITUTE(A1;” “;””))+ 1)
=IF(ISBLANK(A1);0;LEN(TRIM(A1))-LEN(SUBSTITUTE(A1;» «;»»))+1)
Debriefing
The general principle of the formula is simple – the number of words in a cell is always 1 more than the number of spaces between words. Thus, by counting the difference between the length of the original text and its own, but taken without spaces, we will get the number of spaces as a result, i.e. number of words. In case somewhere in the text “for beauty” there are two or three spaces between words or spaces are poked before or after the text, we use the function TRIM (TRIM) from category Text, which removes all extra spaces from the source text, except for single spaces between words. So in our formula:
- DLSTR(TRIMSPACES(A1)) — the length of the original text without extra spaces
- DLSTR(SUBSTITUTE(A1;” “;””)) – the length of the original text, in which all spaces are replaced by emptiness, i.e. removed, i.e. text length without spaces at all
Well, then we count the difference and add 1 to get the desired number of words.
One small problem remains: if the cell is empty, then our formula produces 1. Therefore, we add the function IF (IF), which will check the cell in advance for emptiness using the function ISBLANK (ISBLANK) and output 0 or count the number of words according to our algorithm.
That’s all. Simple and elegant 🙂
- Divide the text into chunks
- Paste text from multiple cells