LEN Function in Excel

LEN in Excel (Table of Contents)

• Introduction to LEN in Excel
• How to Use LEN Function in Excel?

Introduction to LEN in Excel

LEN function in excel, where Len is taken from a length, is used to count the number of characters is there in a cell. LEN function considers special characters, spaces and alphabets and counts all these values if there is a selected cell. The most important thing to know about the LEN function is, it only counts the character length for only one cell. If we select any range of cells, we will get the #Value error.

LEN Formula in Excel:

Below is the LEN Formula.

LEN Function consists of only one parameter, i.e. TEXT

TEXT: The text that you want to find the length of the characters.

How to Use the LEN Function in Excel?

This LEN Function is very simple easy to use. Let us now see how to use the LEN function in Excel with the help of some examples.

Example #1

In the below example, I have used the LEN function to count letters in a cell. “Good Morning!” has 12 letters with a space between them.

I got 13 in the result.

Example #2

In the below example, I have used the LEN function to count letters in a cell. Here I have taken a date as an example.

Even though the above date has 10 characters, I got only 5 as a result. The reason behind it is that LEN Function counts the characters in the value of a cell and not concerned with formatting.

Example #3

Now I will explain the huge benefit of the LEN function when you want to compare two similar sets of data sets.

LEN Function with TRIM is a beautiful combination in excel. We have two similar values in cells A1 and B1, and we need to compare whether both are the same.

The values in Cell A1 & B1 look similar, but when I compare the two values in cell C1, it shows me FALSE. By looking at the outset looks similar but not able to recognize the difference here.

LEN function can save me here. Apply LEN to A1 and find out how many characters are there in the A1 cell

So in cell A1, 14 characters are there.

Apply LEN to B1 to find out how many characters are there in the B1 cell

, and in cell B1, 13 characters are there

, which is why the formula shows it as two different values.

Because of one extra trailing space after the word Excel Formula, it is showing as 14 characters. We can remove the unwanted spaces by using the TRIM function.

Therefore, what TRIM has done here is it removed the unwanted space and returns only the text value; that is why it why now our formula in cell C1 showing both the values as TRUE.

Example #4

Below are the names of the employees, and you needed to extract the Last Name separately.

In the above example, we cannot just apply RIGHT with specified numbers because the last name of every employee is different.

For the first employee last 4 characters are 5, but for the second on it is 11 (including space, i.e. Robort Adam), and for the third one, it is 12 (including space, i.e. J. Johnston)

Here we need to use FIND & LEN function to determine the number of characters from the given text.

and the answer will be:

 

Similarly, We Find the other two

Part 1: This part determines the desired text that you want to extract the characters.

Part 2: LEN function will give you the total number of characters in the list. We will see the detailed article on LEN in the upcoming articles.

The Answer is:

Part 3: The FIND function determines from which number space begins, i.e. The End of the first name. We will see the detailed article on FIND in the upcoming articles.

The Answer is:

Len gives 10 characters, and find gives 5 characters for the first employee. That means LEN(E2) – FIND(F2) (10 – 5) = 5 character from the right side,

the result will be Smith

Len gives 16 characters, and find gives 5 characters for the first employee. That means LEN(E3) – FIND(F3) (16 – 5) = 11 character from the right side

the result will be Robort Adam

Len gives 17 characters, and find gives 5 characters for the first employee. That means LEN(E4) – FIND(F4) (17 – 5) = 12 character from the right side

the result will be J. Johsnston

VBA Code to Use LEN Function

Like in Excel, we can use the MID function in VBA code also. The below code illustrates the usage of the LEN function in VBA macros.

Sub LEN_Function_Example
Dim Length_String as string
Len_String = LEN (“Data Analysis & Reporting”)
Msgbox Len_String
End Sub

If you run the above code message box will display 25 as your result.

Things to Remember

• LEN Function is useful when you want to count how many characters there are in some text.
• Formatting of numbers is not a part of the LEN function, i.e. if you value shows $1500.00, then it will show as 4 only instead of 8.
• Numbers and dates will also return a length.
• If a cell is blank,

it will return 0 in the result.

• If a cell has an error value,

it will return that error in the result.

• You can refer only one cell at a time.

Otherwise, it will show an error as #Value.

Recommended Articles

This has been a guide to LEN Function. Here we discuss the LEN Formula and how to use the LEN function in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

1. AND Function in Excel
2. LOOKUP in Excel
3. VBA Length of String
4. LEN Formula in Excel