LEN in Excel (Table of Contents)
LEN in Excel
LEN Function is a part of text function. LEN function is useful when you want to find the number of characters in the text. Gives the number of characters in given text string including spaces.
For Example, LEN (“Karnataka”) in this formula LEN function will return the value of 9. Since Karnatka has 9 letters, it will return the value as 9.
For Example, LEN (“Karn ataka”) in this formula LEN function will return the value of 10. Even though Karnatka has 9 letters, it will return the value as 10 because it is taking into an account of space as well.
LEN function is also available in VBA too. We will discuss that at the end of this article.
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.
Note: Space is also considered as one character.
How to Use the LEN Function in Excel?
This LEN Function is very simple easy to use. Let us now see how to use LEN function in Excel with the help of some examples.
Example #1
In the below example, I have used LEN function to count letters in a cell. “Good Morning!” has 12 letters with a space between.
I got 13 in the result.
Example #2
In the below example, I have used 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 the 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 LEN function when you want to compare two similar set of data sets.
LEN Function with TRIM is the beautiful combination in excel. We have two similar values in the cells A1 and B1 and we need to compare whether both are same.
The values in Cell A1 & B1 looks similar but when I compare the two values in cell C1 it showing me FALSE as the result. 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 A1 cell
So in cell A1, 14 characters are there
Apply LEN to B1 find out how many characters are there in B1 cell
and in cell B1 13 characters are there
that is why formula showing 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 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 every employee is different.
For the first employee last 4 characters are 5 but for 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: 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 MID function in VBA code also. 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 about the LEN Function
- 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 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 error as #Value.
Recommended Articles
This has been a guide to LEN Function. Here we discuss the LEN Formula and how to use LEN function in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –
