LENGTH Function on Excel (Table of Contents)
LEN in Excel
LEN function is a text function in excel returns the length of a string/ text.
LEN Function in Excel can be used to count the number of character in a text string and able to count letters, numbers, special characters, non-printable characters, and all spaces from an excel cell. In simple words, LENGTH Function is used to calculate the length of a text in an excel cell.
LEN Formula in Excel
LEN formula in excel has only one compulsory parameter i.e. text.
- text: it is the text for which to calculate the length.
How to Use LENGTH Function in Excel?
LENGTH function in Excel is very simple and easy to use. Let understand the working of LENGTH function in Excel by some examples. LEN function Excel can be used as worksheet function and as VBA function.
LENGTH function in Excel as worksheet function.
Length in Excel Example #1
In this LEN example, we are calculating the length of given string or text in column 1 and apply the LEN function in column 2 and it will calculate the length of the Names provided in column 1 as shown in the below table.
LEN in Excel Example #2
We can use the LENGTH function in Excel to calculate the total number of characters in different cells. In this LEN example, we have used LEN Formula in Excel with sum as=SUM(LEN(B17),LEN(C17)) to calculate the total number of characters in different columns or we can also use =LEN(B17)+LEN(C17) to achieve this.
LEN in Excel Example #3
We can use the LEN function Excel to count characters excluding leading and trailing spaces. Here we use Length formula in excel with TRIM to exclude the leading and trailing spaces.
=LEN(TRIM(B31)) and output will be 37.
LEN in Excel Example #4
We can use the LEN function to count the number of characters in a cell excluding all spaces. To achieve this we can use the substitute and LEN formula combination to achieve this.
LEN function can be used as a VBA function.
Dim LENcount As Long
LENcount = Application.Worksheetfunction.LEN(“Alphabet”)
Msgbox(LENcount) // Return the substring “ab” from string “Alphabet” in the message box.
The output will be “8” and printed in the message box.
Things to remember about the Length Function in Excel
- Basically, Length function used to count how many characters there are in some string.
- It can be used on date and numbers.
- Len function does not include formatting length. For example the length of “100” formatted as “$100.00” is still 3).
- If the cell is empty then Length function return 0 as output.
- As shown in row three and six empty string has 0 lengths.
You can download this LENGTH Function in Excel template here – LEN Function Excel Template
This has been a guide to LEN in Excel. Here we discuss the LENGTH Formula in excel and how to use LEN Excel function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –