LEN Excel Function (Length)

Updated on December 28, 2023
Article byTanuj Kumar
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

The LEN function in Excel is also known as the LENGTH Excel function, which identifies the length of a given string. In addition, this function calculates the number of characters in a given string provided as an input. Therefore, it is a TEXT function in Excel. It is also an inbuilt function that can be accessed by typing =LEN( and providing a string as input.

For example, suppose we type the text “Good Night” in cell A1, and we need to know its characters. Then, we can use the LEN Excel function. In such cases, we can use the following function: LEN(A1), which would return the result 10.

LEN in Excel

The LEN function is a text function in Excel that returns a string/ text length.

The LEN function in Excel can count the number of characters in a text string and count letters, numbers, special characters, non-printable characters, and all spaces from an Excel cell. In simple words, the LENGTH function is used to calculate the length of a text in an Excel cell.

LEN Formula in Excel

LEN Formula

The LEN formula in Excel has only one critical parameter, text.

Compulsory Parameter:

  • text: it is the text for which to calculate the length.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Use LENGTH Function in Excel?

The LENGTH function in Excel is very simple and easy to use. Let us understand the working of the LENGTH function in Excel by some examples. The LEN function Excel can be used as a worksheet function and a VBA function.

You can download this LEN Function Excel Template here – LEN Function Excel Template

LENGTH function in Excel as a worksheet function.

Example #1

In this LEN example, we calculate the length of the given string or text in column 1 and apply the LEN function in column 2. As a result, it will calculate the length of the names provided in column 1, as shown below.

LEN Example 1

Result:

LEN Example 1-1

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 the 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 Example 2

Result:

LEN Example 2-1

Example #3

We can use the LEN function Excel to count characters in excelCount Characters In ExcelTo count characters in excel, use the internal formula called “LEN.” This function counts the letters, numbers, characters, and all spaces present in the cell. Since this function counts everything in the cells, this becomes important to know how to exclude some of the alphabets or values.read more, excluding leading and trailing spaces. We use the length formula in Excel with TRIM to exclude the leading and trailing spaces.

=LEN(TRIM(B31)) and output will be 37.

LEN Example 3

Example #4

We can use the LEN function to count the number of characters in a cell, excluding all spaces. We can use the substitute and LEN formula combination to accomplish this.

=LEN(SUBSTITUTE(B45,” “,””))

LEN Example 4

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

  • The LENGTH function counts how many characters there are in some string.
  • We can use it on dates and numbers.
LEN Example 5
  • The LEN function does not include formatting length. So, for example, the length of “100” formatted as “$100.00” is still 3).
LEN Example 6
  • If the cell is empty, the LENGTH function returns 0 as output.
    • As shown in rows three and six, the empty string has 0 lengths.
LEN Example 7

This article is a guide to the LEN Excel Function. We discuss the LENGTH formula in Excel, using the LEN Excel function and Excel example, and downloadable Excel templates. You may also look at these useful functions in Excel: –