How to Use LEN Function in Excel (Formula and VBA Code)

How to Use LEN Function in Excel (Formula and VBA Code)

Excel provides a function called LEN for counting letters, numbers, characters, and all spaces. LEN is a short form of LENGTH. LEN function is used to calculate the length of a text in an excel cell. This article will share the complete idea of how the LEN function works in Excel independently and then with other Excel functions.

Download the Practice WorkBook

LEN Function in Excel (Quick View)

Overview of LEN function

Excel LEN Function: Syntax & Arguments

Excel LEN Function Syntax & Arguments

Summary

Returns the number of characters in a text string.

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 the LEN Function in Excel (8 Examples)

Example 1: Compare Name Length Using LEN Function

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 students have the same length or not.

Compare Name Length Using LEN Function

Step 1: Enter the formula in cell F5 and copy it down up to F12

=LEN(B5)=LEN(D5)

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.

Enter formula using LEN function

Example 2: Count Characters Including Leading and Trailing Spaces

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

Len function including spaces

The used formula for each cell is:

=LEN(B4)

Here text cell reference will vary according to the different strings. From the above picture, the LEN function by default considers leading and trailing spaces.

Example 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. For showing the process let’s consider a dataset of names with spaces. Now our task is to count the characters in each name by ignoring the extra spaces from the names and print them.

Count Characters Excluding Leading and Trailing Spaces

Step 1: Enter the formula in cell C4 and copy it down up to C9

=LEN(TRIM(B4))

Formula Explanation

  • Here TRIM function removes all spaces from text except for single spaces between words.
  • Then LEN function counts the total characters.

Enter formula using LEN and TRIM function

Example 4: Count Number of Characters before or after a Given Character

Let’s assume we have a dataset of some products with ID, Name, and Price. In the ID field, all the IDs are formatted as XXXXX-XXXX-XXX. Thus, we can expect that the first ID should result in 5 characters given that our stopping condition is “-”. If the first group of any product’s id is more or less than 5 characters, then it should be considered as an invalid code.

Count Number of Characters before or after a Given Character

Step 1: Enter the formula in cell E4 and copy it down up to E16

=LEN(LEFT($B4, SEARCH("-", $B4)-1))

Formula Explanation

  • SEARCH(“-“, $B4) this part is searching dash (-) from the B4 cell.
  • LEFT($B4, SEARCH(“-“, $B4)-1) this part Extract text from the left of a string.
  • Lastly, LEN is calculating the total character from the selected group.

Enter formula Using LEN LEFT AND SEARCH function

Example 5: Extract Data from a String Using LEN Function

With the help of the LEN function, we can easily extract any data from any text or string. Let’s consider a dataset of some students with their full names. Now our task is to extract the last name or surname of each student in another column.

 Extract Data from a String Using LEN Function

Step 1: Enter the formula in cell D4 and copy it down up to D9

=RIGHT(C4,LEN(C4)-FIND(" ",C4))

Formula Explanation

  • Here the RIGHT function determines the desired text that you want to extract the characters.
  • Then the LEN function will give you the total number of characters in the list.
  • Lastly, The FIND function determines from which number space begins using FIND(” “, C4).

Enter formula using RIGHT LEN FIND function

Example 6: How to Count Specific Characters in a Cell Using LEN Function

Here we will see how we can use the LEN function to find and count any specific characters. Our task

Sample content
is to find out the number of mobiles in each sales product. The mobile products can be identified by seeing the first letter of ID is “M”

How to Count Specific Characters in a Cell Using LEN Function

Step 1: Enter the formula in cell E4 and copy it down up to E9

=LEN(C4)-LEN(SUBSTITUTE(C4,"M",""))

Formula Explanation

  • Firstly, LEN(C4) counts the total characters of the whole string.
  • Then, using the SUBSTITUTE function, we are removing all occurrences of the letter “M” in C4 by replacing it with an empty string (“”) with this LEN(SUBSTITUTE(C4, “M”,””)) portion.

 Enter formula using LEN and SUBSTITUTE function

Example 7: How to Count Specific Character(s) in a Range Using LEN Function

We can also count the total number of Mobiles using this LEN function. Here we will find out the total number of mobile phones by counting the number of M in the products.

How to Count Specific Character(s) in a Range Using LEN Function

Step 1: Enter the formula in cell F5 and press Enter

=SUMPRODUCT(LEN(C4:C9)-LEN(SUBSTITUTE(C4:C9, "M","")))
Formula Explanation
  • LEN(C4:C9)-LEN(SUBSTITUTE(C4:C9, “M”,””)) this portion finds the total number of matched products from the whole table range.
  • Then lastly, the SUMPRODUCT function calculates the total occurrences.

Formula using SUMPRODUCT and LEN function

Example 8: Use LEN Function Using VBA Code

LEN function can also be used alternatively by a VBA code.  For this, you need to follow the steps.

Step 1: Go to the Developer tab and click on Macros (Shortcut Alt + F8)

Open Macros

Step 2: Give any name and click on Create button

Give any name and then click on create

Step 3: Enter the code in the VBA window

VBA code for LEN function

Code:

Sub Len_Function()
Dim Var_Ex1 As String
Var_Ex1 = "This is a text sample"
MsgBox Len(Var_Ex1)
End Sub

Step 4: Then click on the Run button

Click on run button

Step 5: Now see the output in a pop-up window

VBA output for LEN function

Things to Remember

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.

Conclusion

That’s it all about the LEN function. Here I have tried to give a piece of proper knowledge about this function and its different applications. I have shown multiple methods with their respective examples but there can be many other iterations depending on numerous situations. If you have any inquiries or feedback, please let us know in the comment section.

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

ExcelDemy
Logo