Updated May 30, 2023
LEN in Excel (Table of Contents)
Introduction to LEN in Excel
LEN function in Excel, where Len is taken from a length, is used to count the number of characters in a cell. LEN function considers special characters, spaces, and alphabets and counts all these values if a selected cell exists. 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 you want to find the length of the characters.
How to Use the LEN Function in Excel?
This LEN Function is very simple and 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 on 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. The reason behind it is that LEN Function counts the characters in the value of a cell and is 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 comparing them in Cell C1 shows me FALSE. Looking at the outset looks similar, but not able to recognize the difference here.
The LEN function can save me here. Apply LEN to A1 and determine how many characters are 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 unwanted spaces by using the TRIM function.
Therefore, TRIM has removed the unwanted space and returned only the text value; now, our formula in cell C1 shows both 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, the last 4 characters are 5, but for the second one,e it is 11 (including space, i.e., Robert 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 from 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 Finds 5 for the first employee. That means LEN(E2) – FIND(F2) (10 – 5) = 5 characters from the right side; the result will be Smith
Len gives 16 characters and Finds 5 for the first employee. That means LEN(E3) – FIND(F3) (16 – 5) = 11 characters from the right side
the result will be Robert Adam
Len gives 17 characters and Find 5 for the first employee. That means LEN(E4) – FIND(F4) (17 – 5) = 12 character from the right side
the result will be J. Johnston
VBA Code to Use LEN Function
We can also use the MID function in VBA code, like Excel. 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 count the number of characters in some text.
- Formatting of numbers is not a part of the LEN function, i.e., if your 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.
- A cell with an error value will return that error in the result.
- You can refer to 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 –
