Excel provides a function called **LEN **for counting letters, numbers, characters, and all spaces. **LEN **is a short form of **LENGTH**. **LEN **function is used to calculate the length of a text in an excel cell. This article will share the complete idea of how the **LEN **function works in Excel independently and then with other Excel functions.

**Table of Contents**hide

## Download the Practice WorkBook

**LEN Function in Excel (Quick View)**

**Excel LEN Function: Syntax & Arguments**

**Summary**

Returns the number of characters in a text string.

**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.

**How to Use the LEN Function in Excel (8 Examples)**

**Example 1: Compare Name Length Using LEN Function**

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 students have the same length or not.

**Step 1: **Enter the formula in cell **F5** and copy it down up to **F12**

`=LEN(B5)=LEN(D5)`

**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.

**Example 2: Count Characters Including Leading and Trailing Spaces**

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

The used formula for each cell is:

`=LEN(B4)`

Here text cell reference will vary according to the different strings. From the above picture, the **LEN **function by default considers leading and trailing spaces.

**Example 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. For showing the process let’s consider a dataset of names with spaces. Now our task is to count the characters in each name by ignoring the extra spaces from the names and print them.

**Step 1:** Enter the formula in cell **C4 **and copy it down up to **C9**

`=LEN(TRIM(B4))`

**Formula Explanation**

- Here
**TRIM**function removes all spaces from text except for single spaces between words. - Then
**LEN**function counts the total characters.

**Example 4: Count Number of Characters before or after a Given Character**

Let’s assume we have a dataset of some products with ID, Name, and Price. In the ID field, all the IDs are formatted as XXXXX-XXXX-XXX. Thus, we can expect that the first ID should result in 5 characters given that our stopping condition is **“-”**. If the first group of any product’s id is more or less than 5 characters, then it should be considered as an invalid code.

**Step 1:** Enter the formula in cell **E4 **and copy it down up to **E16**

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

**Formula Explanation**

**SEARCH(“-“, $B4)**this part is searching dash (-) from the**B4**cell.**LEFT($B4, SEARCH(“-“, $B4)-1)**this part Extract text from the left of a string.- Lastly, LEN is calculating the total character from the selected group.

**Example 5: Extract Data from a String Using LEN Function**

With the help of the **LEN **function, we can easily extract any data from any text or string. Let’s consider a dataset of some students with their full names. Now our task is to extract the last name or surname of each student in another column.

**Step 1:** Enter the formula in cell **D4** and copy it down up to **D9**

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

**Formula Explanation**

- Here the
**RIGHT**function determines the desired text that you want to extract the characters. - Then the
**LEN**function will give you the total number of characters in the list. - Lastly, The
**FIND**function determines from which number space begins using**FIND(” “, C4)**.

**Example 6: How to Count Specific Characters in a Cell Using LEN Function**

Here we will see how we can use the **LEN **function to find and count any specific characters. Our task

**“M”**

**Step 1:** Enter the formula in cell **E4** and copy it down up to **E9**

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

**Formula Explanation**

- Firstly,
**LEN(C4)**counts the total characters of the whole string. - Then, using the
**SUBSTITUTE**function, we are removing all occurrences of the letter “M” in C4 by replacing it with an empty string (“”) with this**LEN(SUBSTITUTE(C4, “M”,””))**portion.

**Example 7: How to Count Specific Character(s) in a Range Using LEN Function**

We can also count the total number of Mobiles using this LEN function. Here we will find out the total number of mobile phones by counting the number of M in the products.

**Step 1: **Enter the formula in cell **F5 **and press Enter

`=SUMPRODUCT(LEN(C4:C9)-LEN(SUBSTITUTE(C4:C9, "M","")))`

**Formula Explanation**

**LEN(C4:C9)-LEN(SUBSTITUTE(C4:C9, “M”,””))**this portion finds the total number of matched products from the whole table range.- Then lastly, the
**SUMPRODUCT**function calculates the total occurrences.

**Example 8: Use LEN Function Using VBA Code**

**LEN **function can also be used alternatively by a VBA code. For this, you need to follow the steps.

**Step 1:** Go to the **Developer **tab and click on **Macros **(Shortcut **Alt + F8**)

**Step 2:** Give any name and click on **Create **button

**Step 3:** Enter the code in the VBA window

**Code:**

```
Sub Len_Function()
Dim Var_Ex1 As String
Var_Ex1 = "This is a text sample"
MsgBox Len(Var_Ex1)
End Sub
```

**Step 4:** Then click on the **Run **button

**Step 5:** Now see the output in a pop-up window

**Things to Remember**

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

That’s it all about the **LEN **function. Here I have tried to give a piece of proper knowledge about this function and its different applications. I have shown multiple methods with their respective examples but there can be many other iterations depending on numerous situations. If you have any inquiries or feedback, please let us know in the comment section.