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

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.

Result:

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.

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.

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 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.

  • The LEN function does not include formatting length. So, for example, the length of “100” formatted as “$100.00” is still 3).

  • 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.

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: –

  • As shown in rows three and six, the empty string has 0 lengths.

  • VBA LENPROPER FunctionROUND Excel Function | ExamplesAGGREGATE Excel FunctionCAGR Formula in Excel