LEN Excel Function (Length)

Last Updated :

-

Blog Author :

Tanuj Kumar

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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.

Table of contents

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.

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.

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 excel, 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