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.
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)
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.
- Use Fill Handle to AutoFill the rest cells.
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:
- Input the following formula to count the characters in a cell.
=LEN(B5)
- Hit the ENTERÂ button.
- Now, AutoFill the rest cells.
- 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))
- Next, press on the ENTER button to have the character numbers excluding leading and trailing spaces.
- Finally, AutoFill the remaining cells.
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))
- Afterward, press on ENTER to have the output.
- You can AutoFill the rest cells.
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))
- Hit the ENTER button to have the result.
- After that, use Fill Handle to AutoFill the rest cells.
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",""))
- Hit the ENTERÂ button.
- Lastly, AutoFill the rest cells.
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","")))
- Now, press the ENTER button to have the total number.
Practice Section
For more expertise, you can practice here.
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.
