Show All

Hide All

Count the number of words in a cell

You can count the number of words in a cell by using the following formula:

=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)

 Note   To avoid a circular reference, make sure you enter the formula in a cell outside the cell for which you are counting the words.

In this example, imagine that cell A1 contains the text "Have a nice day!", and cell A2 contains the formula. The formula returns a value of 4 to reflect that the cell contains four words separated by spaces. It makes no difference if words are separated by multiple spaces or if words start or end with a space. The TRIM function removes extra spaces, starting spaces, and ending spaces in the text of the cell.

Count the number of words in a range of cells

You can count the number of words in a worksheet by using a similar formula, but you must enter it as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.):

{=SUM(IF(LEN(TRIM(A1:D7))=0,0,LEN(TRIM(A1:D7))-LEN(SUBSTITUTE(A1:D7," ",""))+1))}

  Notes  

• To avoid a circular reference, make sure you enter the formula in a cell outside the range for which you are counting the words.
• To enter an array formula, select the cell that contains the formula, press F2, and then press CTRL+SHIFT+ENTER.

In this example, imagine that cells A1 to D7 contain the words you want to count. To get the total number of words in the specified range, the array formula counts the words in each cell of that range, and the SUM function then adds up the values for each cell to get the total.

Tip