Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
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 –