Count the number of words in an Excel cell

This example describes how to count the number of words in a cell.

1. Function TRIM (TRIM) removes all extra spaces from a string, if any.

   =TRIM(A1)

   =СЖПРОБЕЛЫ(A1)

2. To get the length of a string with a normal number of spaces, let’s combine the functions LEN (DLSTR) and TRIM (TRIM SPACES).

   =LEN(TRIM(A1))

   =ДЛСТР(СЖПРОБЕЛЫ(A1))

   

3. Function SUBSTITUTE (SUBSTITUTE) replaces the text in the cell with new text. We use it to get a string without spaces.

   =SUBSTITUTE(A1," ","")

   =ПОДСТАВИТЬ(A1;" ";"")

   

4. To get the length of a string without spaces, let’s combine the functions LEN (DLSTR) and SUBSTITUTE (SUBSTITUTE).

   =LEN(SUBSTITUTE(A1," ",""))

   =ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))

   

5. Now you will see a simple trick. To get the number of words, we subtract the length of the string without spaces (10) from the length of the string with the normal number of spaces (12) and add 1.

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

   =ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))+1