**Microsoft Excel** provides a function called **LEN **for counting letters, numbers, characters, and all spaces. **LEN **is a short form of **LENGTH**. The **LEN **function is used to calculate the length of a text in an **Excel **cell. This article will share the complete idea of how to use the **LEN **function in **Excel **with 7 simple examples.

In the following section, I have tried to show an overview of the use of the **LEN **function.

**Table of Contents**hide

## Download Practice Workbook

**Introduction to LEN Function in Excel**

**Definition**

The **LEN **function in **Excel **is a function that returns the length of a given string. The function is useful for a variety of purposes, including for finding the number of characters in a given cell or range of cells, or for finding the number of characters in a given string of text.

**Syntax**

The **LEN **function is described with the following syntax:

`=LEN (text)`

**Arguments**

Argument |
Required or Optional |
Value |
---|---|---|

text |
Required | The text for which to calculate length. |

**Note: **

**LEN**reflects the length of text as a number.- This function works with numbers, but number formatting is not included.
**LEN**function returns zero in terms of empty cells.

## 7 Simple Examples to Use LEN Function in Excel

We know that the **LEN **function is used to count the length of a cell. This **LEN **function can be applied in various cases. The **LEN **function takes into account a cell value and returns the length of that cell. Let’s dive into detail.

### 1. Compare Between Name Lengths

Using the **LEN **function, we can easily find out the total length of any string. Now let’s consider we have a dataset of two student groups. Now, our task is to find out whether the names of the students have the same length or not.

**Steps**:

- Select a cell and input the following formula to compare between name lengths.

`=LEN(B5)=LEN(C5)`

**Formula Explanation**

**LEN(B5)**will return the total length of the**B5**cell which is 4.**LEN(D5)**will return the total length of the**D5**cell which is also 4.- Then
**=LEN(B5)=LEN(D5)**will compare whether the number is the same or not.

- Now, press
**ENTER**to have the comparison output.

- Use
**Fill Handle**to**AutoFill**the rest cells.

**Read More:** **How to Use TEXT Function in Excel (10 Examples)**

### 2. Count Characters Including Leading and Trailing Spaces

As I have mentioned earlier the **LEN **function considers spaces at the time of counting characters, we are going to test that here.

**Steps**:

- Input the following formula to count the characters in a cell.

`=LEN(B5)`

- Hit the
**ENTER**button.

- Now,
**AutoFill**the rest cells.

- In cell
**C5**, the total characters’ number is 32 as there are no leading and trailing spaces. - For the string written in
**B6**, the output shows**33**characters in cell**C6**as there is a leading space. - For the string written in
**B7**, the output shows**33**characters in cell**C7**as there is a trailing space. - For the string written in
**B8**, the output shows**34**characters in cell**C6**as there is not only a leading but also a trailing space.

### 3. Count Characters Excluding Leading and Trailing Spaces

From the previous example, we can see that the **LEN **function considers spaces. But we can ignore these spaces using the **TRIM function**. Let’s consider a dataset of names with spaces to show the process. Now, our task is to count the characters in each name by ignoring the extra spaces from the names and printing them.

**Steps**:

- First of all, select a cell (i.e.
**C5**) and input the following formula to compare between name lengths.

`=LEN(TRIM(B5))`

- Next, press on the
**ENTER**button to have the character numbers excluding leading and trailing spaces.

- Finally,
**AutoFill**the remaining cells.

### 4. Count Number of Characters Before or After a Given Character

With the help of the **LEN **function, we can also count the number of characters before or after a certain character. The whole process is described below.

**Steps**:

- Input the following formula in a selected cell.

`=LEN(LEFT($B5, SEARCH("-", $B5)-1))`

- Afterward, press on
**ENTER**to have the output.

- You can
**AutoFill**the rest cells.

**Read More:** **How to Use SEARCH Function in Excel (3 Examples)**

**Similar Readings**

**How to Use CODE Function in Excel (5 Examples)****Use Excel EXACT Function (6 Suitable Examples)****How to Use FIXED Function in Excel (6 Suitable Examples)****Use CLEAN Function in Excel (10 Examples)**

### 5. Extract Data from a String

We can also use the **LEN **function to extract data from a string. For this, we need to define a certain character which will be represented as a basic criterion.

**Steps**:

- Pick a cell and input the following formula to count the characters in a cell.

`=RIGHT(C5,LEN(C5)-FIND(" ",C5))`

- Hit the
**ENTER**button to have the result.

- After that, use
**Fill Handle**to**AutoFill**the rest cells.

### 6. Count Specific Character in a Cell

We can also define a character and count that specified character in a selected cell. Just follow the following procedures to do so.

**Steps**:

- Select a cell and input the following formula to count a specified character in that cell.

`=LEN(C5)-LEN(SUBSTITUTE(C5,"M",""))`

- Hit the
**ENTER**button.

- Lastly,
**AutoFill**the rest cells.

### 7. Summarize Specific Character Count in a Range

We can also count the total number of a specific character in a range. Here, we will find out the total number of mobile phones by counting the number of M in the products.

**Steps**:

- First of all, select a cell (i.e.
**C5**) and input the following formula to compare between name lengths.

`=SUMPRODUCT(LEN(C5:C10)-LEN(SUBSTITUTE(C5:C10, "M","")))`

- Now, press the
**ENTER**button to have the total number.

**Practice Section**

For more expertise, you can practice here.

## Common Errors with Excel LEN Function

Common Errors |
When They Show |
---|---|

#NAME |
This will appear if you did not enter the function name properly. |

#REF! |
It will appear if a LEN function formula is used between two different workbooks and the source workbook is closed. |

**Conclusion**

At the end of this article, I like to add that I have tried to explain the complete idea of how to use **the** **LEN function** in **Excel **with 7 simple examples. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.