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)
Excel LEN Function: Syntax & Arguments
Returns the number of characters in a text string.
|Argument||Required or Optional||Value
|text||Required||The text for which to calculate length.|
- 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.
Step 1: Enter the formula in cell F5 and copy it down up to F12
- 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.
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.
The used formula for each cell is:
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.
Step 1: Enter the formula in cell C4 and copy it down up to C9
- Here TRIM function removes all spaces from text except for single spaces between words.
- Then LEN function counts the total characters.
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.
Step 1: Enter the formula in cell E4 and copy it down up to E16
=LEN(LEFT($B4, SEARCH("-", $B4)-1))
- 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.
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.
Step 1: Enter the formula in cell D4 and copy it down up to D9
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
Step 1: Enter the formula in cell E4 and copy it down up to E9
- 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.
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.
Step 1: Enter the formula in cell F5 and press Enter
- 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.
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)
Step 2: Give any name and click on Create button
Step 3: Enter the code in the VBA window
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
Step 5: Now see the output in a pop-up window
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.|
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.