# 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

## 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