Title: How to count text in Excel ? Caculate Specific text in Excel with Formulae
Headings: How to count text in Excel?
How to count text in Excel?
Now let’s go through the following steps to count text in Excel!
Let’s Count cells that are not blank
=COUNTA (B2:C6,F2:G6)
How to count the total number of words in a cell
=LEN (TRIM (A1))-LEN (SUBSTITUTE (A1,” “,””))+1
=LEN (TRIM (A1))-LEN (SUBSTITUTE (A1,” “,””))+1
LEN (SUBSTITUTE (A1,” “,””))

Content: =LEN (TRIM (A1))-LEN (SUBSTITUTE (A1,” “,””))+1
Now press ENTER. It is showing the result 7. So there are 7 words in cell A1. Remember: How this word counting formula works? =LEN (TRIM (A1))-LEN (SUBSTITUTE (A1,” “,””))+1
First, we use the SUBSTITUTE function to remove all spaces in the cell A1 by replacing them with an empty text string (“”) for the LEN function to return the length of the string without spaces: LEN (SUBSTITUTE (A1,” “,””))
After that, we subtract the string length without spaces from the total length of the string and add 1 to the final word count, since the number of words in a cell equals to the number of spaces plus 1. Additionally, we use the TRIM function to eliminate extra spaces in the cell, if any. Sometimes a worksheet may contain a lot of invisible spaces, for example, two or more spaces between words or space characters accidentally typed at the beginning or end of the text (i.e. leading and trailing spaces). All those extra spaces can throw your word count off.