How to Use LEN Function in Excel (7 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel provides a function called LEN for counting letters, numbers, characters, and all spaces. LEN is a short form of LENGTH. The LEN function is used to calculate the length of a text in an Excel cell. This article will share the complete idea of how to use the LEN function in Excel with 7 simple examples.

In the following section, I have tried to show an overview of the use of the LEN function.

Excel LEN Function


Introduction to LEN Function in Excel

Definition

The LEN function in Excel is a function that returns the length of a given string. The function is useful for a variety of purposes, including for finding the number of characters in a given cell or range of cells, or for finding the number of characters in a given string of text.


Syntax

The LEN function is described with the following syntax:

=LEN (text)

Arguments

Argument Required or Optional Value
text Required The text for which to calculate length.

Note

  • LEN reflects the length of text as a number.
  • This function works with numbers, but number formatting is not included.
  • LEN function returns zero in terms of empty cells.

How to Use LEN Function in Excel: 7 Simple Examples

We know that the LEN function is used to count the length of a cell. This LEN function can be applied in various cases. The LEN function takes into account a cell value and returns the length of that cell. Let’s dive into detail.


1. Compare Between Name Lengths

Using the LEN function, we can easily find out the total length of any string. Now let’s consider we have a dataset of two student groups. Now, our task is to find out whether the names of the students have the same length or not.

Steps:

  • Select a cell and input the following formula to compare between name lengths.
=LEN(B5)=LEN(C5)

Compare Between Name Lengths

Formula Explanation

  • LEN(B5) will return the total length of the B5 cell which is 4.
  • LEN(D5) will return the total length of the D5 cell which is also 4.
  • Then =LEN(B5)=LEN(D5) will compare whether the number is the same or not.
  • Now, press ENTER to have the comparison output.

 Excel LEN Function


2. Count Characters Including Leading and Trailing Spaces

As I have mentioned earlier the LEN function considers spaces at the time of counting characters, we are going to test that here.

Steps:

=LEN(B5)

Count Characters Including Leading and Trailing Spaces

  • Hit the ENTER button.

  • Now, AutoFill the rest cells.

Excel LEN Function

  • In cell C5, the total characters’ number is 32 as there are no leading and trailing spaces.
  • For the string written in B6, the output shows 33 characters in cell C6 as there is a leading space.
  • For the string written in B7, the output shows 33 characters in cell C7 as there is a trailing space.
  • For the string written in B8, the output shows 34 characters in cell C6 as there is not only a leading but also a trailing space.

3. Count Characters Excluding Leading and Trailing Spaces

From the previous example, we can see that the LEN function considers spaces. But we can ignore these spaces using the TRIM function. Let’s consider a dataset of names with spaces to show the process. Now, our task is to count the characters in each name by ignoring the extra spaces from the names and printing them.

Steps:

  • First of all, select a cell (i.e. C5) and input the following formula to compare between name lengths.
=LEN(TRIM(B5))

Count Characters Excluding Leading and Trailing Spaces

  • Next, press on the ENTER button to have the character numbers excluding leading and trailing spaces.

  • Finally, AutoFill the remaining cells.

Excel LEN Function


4. Count Number of Characters Before or After a Given Character

With the help of the LEN function, we can also count the number of characters before or after a certain character. The whole process is described below.

Steps:

  • Input the following formula in a selected cell.
=LEN(LEFT($B5, SEARCH("-", $B5)-1))

Count Number of Characters Before or After a Given Character

  • Afterward, press on ENTER to have the output.

  • You can AutoFill the rest cells.

Excel LEN Function


5. Extract Data from a String

We can also use the LEN function to extract data from a string. For this, we need to define a certain character which will be represented as a basic criterion.

Steps:

  • Pick a cell and input the following formula to count the characters in a cell.
=RIGHT(C5,LEN(C5)-FIND(" ",C5))

Extract Data from a String

  • Hit the ENTER button to have the result.

  • After that, use Fill Handle to AutoFill the rest cells.

Excel LEN Function


6. Count Specific Character in a Cell

We can also define a character and count that specified character in a selected cell. Just follow the following procedures to do so.

Steps:

  • Select a cell and input the following formula to count a specified character in that cell.
=LEN(C5)-LEN(SUBSTITUTE(C5,"M",""))

Count Specific Character in a Cell

  • Hit the ENTER button.

  • Lastly, AutoFill the rest cells.

 Excel LEN Function


7. Summarize Specific Character Count in a Range

We can also count the total number of a specific character in a range. Here, we will find out the total number of mobile phones by counting the number of M in the products.

Steps:

  • First of all, select a cell (i.e. C5) and input the following formula to compare between name lengths.
=SUMPRODUCT(LEN(C5:C10)-LEN(SUBSTITUTE(C5:C10, "M","")))

Summarize Specific Character Count in a Range

  • Now, press the ENTER button to have the total number.

 Excel LEN Function


Practice Section

For more expertise, you can practice here.

Practice Section


Common Errors with Excel LEN Function

Common Errors When They Show
#NAME This will appear if you did not enter the function name properly.
#REF! It will appear if a LEN function formula is used between two different workbooks and the source workbook is closed.

Download Practice Workbook


Conclusion

At the end of this article, I like to add that I have tried to explain the complete idea of how to use the LEN function in Excel with 7 simple examples. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo