Method 1 – Count the Number of Words in a Cell with the LEN Function Use the following formula in the result cell: =LEN(TRIM(C5))-LEN(SUBSTITUTE(C5," ",""))+1 Formula Breakdown: The SUBSTITUTE function replaces all the spaces in the text in the C5 cell. The LEN function returns...
If you prefer word problems, think of the formula this way. If the cell is empty, make the word count = 0. Otherwise, remove the extra spaces and count the characters in the cell. Store that value as “A.” Now, remove all spaces in that cell and count the characters again. Store ...
Press theAlt + Qkeys to close the Microsoft Visual Basic for Applications window. Select a blank cell to put the result, and then enter or copy the below formula: ●Count a range of cells:=CountWords(A2:A4) ●Count a single cell:=CountWords(A2) ...
Choose what you need to count: characters, specific symbols, or words. Decide if you want the result as a formula and whether you need a backup worksheet. Click Insert results. See a more detailed description here. I need the result as a formula, can the add-in do that? Yes. The add...
Formula to Get Word Count in Excel Example 1 – When there is a single space between words Example 2: When there are multiple spaces between words Using VBA Custom Function to Count Words in Excel Formula to Get Word Count in Excel Before I give you the exact formula, let’s quickly cov...
Counting Words There is no built-in Excel function to count the number of words in a cell. However, we can count the number of spaces, which can tell us how many words are in a cell: =LEN(TRIM(B3))-LEN(SUBSTITUTE(B3," ",""))+1 Let’s see how this formula works. Remove All...
Count words in a range of cells Now let’s move to the next level. Here you will need to count the words already in a range of cells instead of one cell. Good news! You can use the same formula (with a slight change) that we used above. Here it is:=SUMPRODUCT(LEN(A1:A3)-LEN...
Define the function =COUNTIF Define the range $A$2:$A$10. Tip: Use a fixed range so that you can copy the formula. Define the criteria "=*delete*" Tip: Instead of a specific word, use a link to another cell. ("=*"&C2&"*") That way we can make one formula work for any ...
4. Next, we determine for each cell in this range how many words it contains. To ignore a cell that contains a formula, add the following code line between For Each and Next (only if cell.HasFormula is false we continue). IfNotcell.HasFormulaThen ...
In the above screenshot, only the word "Life" is counted, but not "life". However, you can force the formula to disregard the letter case with the help of either the UPPER or LOWER function. To see how this works, please visitHow to count specific words in a range. ...