**Are you confused about the Len Function in Excel? **

The Len function helps you find the number of characters in a excel cell. Just type = LEN ( select any cell with characters), it will show you the number of characters in it.

The Len Function is categorized under the Excel String/Text function. The function returns the length of the specified string or in other words length of the text. It can be used as a worksheet function and a VBA function in Excel. As a WS function, the LEN function can be entered as a part of a formula in a cell of a worksheet. As a VBA function, the user can use this function in a macro mode that is entered through the Microsoft Visual Basic Editor.

**Using Len Function In Excel**

**Syntax: Len Formula**

LEN (text)

**Parameters:**

- Text – The string to return the length for

**Returns:**

The LEN function returns a numeric value

**Applies to:**

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

**Important Note While using the Len Function**

Len function will count the number blank spaces as characters too. For example in a cell. As you can see the first case, the function has also calculated the two spaces in between.

Hence be care full while making assumptions on the character length.

**Example** Len Function in Excel

Let’s see how we can test a few conditions. Suppose we are given the following values:

We will get the results below:

In the examples above,

- Spaces are counted as characters. Hence, the function returned 13 for the first argument.
- The length of the empty string is 0.

**How to Exclude Spaces while using the function**

This can be a little tricky to understand at first but get the logic here:

- Lets say we have spaces between the characters like our previous example.
- Now the spaces in excel can be denoted as (“ ”), meaning blank space
- And the notation without space would be (“”)
- So we can substitute, the “ “ with “”, using the substitute function

Here is how it will look: Now the Len function will give you the same answer as if there was no space.

A similar relative method can be used to substitute punctuation, in the example below we have substituted the “-“ with “”.

Which means, we want excel to ignore the “–“ in the counting

**Real Application of Len function**

Len function although very sparsely used in daily life, can be used to build some logic.

For example: Lets design a cell in an excel tempalte, which checks, if the characters of an ID card is entered correct or not.

- Lets say the ID card should have 8 characters only
- If the characters are less than 8 or more than 8 are entered, then it throws an error

- So you can see we used a logical operator and the logical test uses len function counts the number of characters
- Which we are testing it to be equal to 8
- If it returns true , then no error is shown or its “”- blank

- Else it throws our error message

This was just an illustration of a simple use case of Len function, based on the industry you are working. There can be many such interesting applications of it. You can check out such applications here :

## Functions Similar to Len

There are a few different ways to get the number of characters in a text string in Microsoft Excel, depending on the specific task or scenario. Some alternatives to the LEN function include:

- The LENGTH function: This function is similar to the LEN function in that it returns the number of characters in a text string. The syntax for the LENGTH function is =LENGTH(text)
- The CHAR function: This function returns the character from the specified ascii code. The syntax is =CHAR(number)
- The CODE function: This function returns the ascii code for the first character of a text. The syntax is =CODE(text)
- The SUBSTITUTE function: This function is useful if you want to replace a certain character or set of characters with another character or set of characters, while also counting the number of replacements made. The syntax is =SUBSTITUTE(old_text, old_char, new_char, [instance_num]).
- The REPLACE function : This function is similar to substitute function, but it replaces a specified number of characters, starting at the specified position. The syntax is =REPLACE(old_text, start_num, num_chars, new_text).

## Conclusion

In summary, the LEN function in excel is a simple function that counts the number of characters in a text string, it is useful to validate data, truncate or concatenate strings, and also to extract certain characters from a text string.