How to count the number of words in Excel 

Counting the number of words refers to determining the total count of words within a given text or cell. It is a useful feature when dealing with text data analysis, and document processing, or when you need to calculate metrics such as readability, average word length, or word frequency. When working with large documents or text data, counting the number of words helps in understanding the document’s length, and complexity, or identifying patterns and trends. 

We have a dataset where each text is contained within a single cell in Excel. Our goal is to determine the word count for each text. To achieve this, we can utilize a formula that calculates the word count by comparing the total number of characters in the cell (including spaces) with the number of characters in the cell after removing all spaces. By applying this formula to each cell in the dataset, you will obtain the desired word count for each text. 

Method 1: How to count the number of words in Excel using Length Function 

This method will count the number of words in the Excel cell including all spaces as well. If you want to exclude the spaces while counting the number of words then please skip to method 2. 

Step 1 – Type Length 

• First type length in the cell A2 

Step 2 – Utilize length Function 

• Now type Formula “=Len(B1)” in the cell B2 
• The “=LEN(B1)” formula calculates the length of the text in cell B1 using the “LEN” function. It will return the number of characters in cell B1 

Step 3 – Press Enter Key 

• After typing the formula, Press the Enter key and get the result 

Method 2: How to count the number of words in Excel using the Trim and Length Function 

This method will exclude the spaces in between the words and count the  

Step 1 – Type Length 

• Type length in the cell A2  

Step 2 – Utilize the Length Function 

• Now type Formula “=Len(B1)” in the cell B2 

• The “=LEN(B1)” formula calculates the length of the text in cell B1 using the “LEN” function. It will return the number of characters in cell B1 having space before the text 

Step 3 – Press Enter Key 

• After typing the formula, press the Enter key and it will give you the length of the text having space before it 

Step 4 – Type Trimmed 

• Now type Trimmed in  cell A5 

Step 5 – Type Length 

• Afterward, the type Length in  cell A5 

Step 6 – Utilize Trim Function 

• Now Type Formula “=Trim(B1)” in the cell B5 

• The “=TRIM(B1)” formula removes any leading or trailing spaces from the text in cell B1 using the “TRIM” function. Here it removes the space before the text 

Step 7 – Press Enter Key 

• After typing the Formula, press the Enter Key and it will give you the text having no space before it 

Step 8 – Type Length 

• Type length in the cell A6 

Step 9 – Press Enter Key 

• After typing the formula, Press the Enter key and get the result 

Method 3: How to count the number of words in Excel using Substitute and Length Function 

Step 1 – Type Length 

• First type length in the cell A2 

Step 2 – Utilize length Function 

• Now type Formula “=Len(B1)” in the cell B2 

• The “=LEN(B1)” formula calculates the length of the text in cell B1 using the “LEN” function. It will return the number of characters in cell B1 with the space 

Step 3 – Press Enter Key 

• After typing the formula, Press the Enter key, and it will give you the length of the text with space 

Step 4 – Type No Space 

• Afterward, the type No Space in cell A6 

Step 5 – Utilize Substitute Function 

• Now type Formula “=SUBSTITUTE(B1,” “,””)” cell B6 

• The “=SUBSTITUTE(B1, ” “, “”)” formula replaces all occurrences of spaces in the text of cell B1 with an empty string. This formula effectively removes all spaces from the text in cell B1. 

Step 6 – Press Enter Key 

• After typing the formula, Press the Enter key and it will give you the text with no space 

Step 7 – Type Length 

• Now type length in  cell A7 

Step 8 – Utilize length Function 

• Now type Formula “=Len(B6)” in the cell B7 
• The “=LEN(B6)” formula calculates the length of the text in cell B6 using the “LEN” function. It will return the number of characters in cell B6 with the space 

Step 9 – Press Enter Key 

• After typing the formula, Press the Enter key, and it will give you the length of the text with space