This an overview of the LEN function.
Syntax
The LEN function is described with the following syntax:
=LEN (text)Arguments
| Argument | Required or Optional | Value |
|---|---|---|
| text | Required | The text to calculate the length. |
Note:
- LEN reflects the length of text as a number.
- This function works with numbers, but number formatting is not included.
- The LEN function returns zero for empty cells.
Example 1 – Compare Name Length
The sample dataset showcases two groups of students.
Steps:
- Select a cell and enter the following formula.
=LEN(B5)=LEN(C5)
Formula Breakdown
- LEN(B5) will return the total length of B5: 4.
- LEN(D5) will return the total length of D5: 4.
- =LEN(B5)=LEN(D5) will compare the numbers.
- Press ENTER to see the output.
- Drag down the Fill Handle to AutoFill the rest of the cells.
Example 2 – Count the Characters Including Leading and Trailing Spaces
Steps:
- Enter the following formula to count the characters in a cell.
=LEN(B5)
- Press ENTER to see the output.
- Drag down the Fill Handle to AutoFill the rest of the cells.
- In C5, the total is 32: there are no leading and trailing spaces.
- In B6, the output is 33 characters: in C6 as there is a leading space.
- In B7, the output is 33 characters: in C7 as there is a trailing space.
- In B8, the output 34 characters: in C6 as there is a leading and a trailing space.
Example 3 – Count Characters Excluding Leading and Trailing Spaces
Steps:
- Select a cell (C5) and enter the following formula.
=LEN(TRIM(B5))
- Press ENTER to see the number of characters excluding leading and trailing spaces.
- Drag down the Fill Handle to AutoFill the rest of the cells.
Example 4 – Count the Number of Characters Before or After a Given Character
Steps:
- Enter the following formula in a selected cell.
=LEN(LEFT($B5, SEARCH("-", $B5)-1))
- Press ENTER to see the output.
- Drag down the Fill Handle to AutoFill the rest of the cells.
Example 5 – Extract Data from a String
Steps:
- Select a cell and enter the following formula.
=RIGHT(C5,LEN(C5)-FIND(" ",C5))
- Press ENTER to see the output.
- Drag down the Fill Handle to AutoFill the rest of the cells.
Example 6 – Count Specific Characters in a Cell
Steps:
- Select a cell and enter the following formula.
=LEN(C5)-LEN(SUBSTITUTE(C5,"M",""))
- Press ENTER to see the output.
- Drag down the Fill Handle to AutoFill the rest of the cells.
Example 7 – Summarize a Specific Character Count in a Range
Find the total number of mobile phones by counting the number of Ms.
Steps:
- Select a cell (C5) and enter the following formula.
=SUMPRODUCT(LEN(C5:C10)-LEN(SUBSTITUTE(C5:C10, "M","")))
- Press ENTER to see the output.
Practice Section
Practice here.
Common Errors with Excel LEN Function
| Common Errors | When They Show |
|---|---|
| #NAME | The function name is incorrect. |
| #REF! | The formula is used in two different workbooks and the source workbook is closed. |
Download Practice Workbook
<< Go Back to Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Cell [B29] = DDDF-98765432-110 [sample data in cell B29]
Right(), Left() and Len() formula is required to obtain: 98765432
data between the two dashes is required to be retrieved, please.
I have solved it as:
=LEFT(RIGHT(B29,LEN(B29)-FIND(“-“,B29)),FIND(“-“,RIGHT(B29,LEN(B29)-FIND(“-“,B29)))-1)
Hello Falak Niaz,
Certainly! Your formula correctly extracts the data between the two dashes. Your feedback and suggestion will be helpful for other users.
If you want you can simplify it by using MID function.
=MID(B29, FIND(“-“, B29) + 1, FIND(“-“, B29, FIND(“-“, B29) + 1) – FIND(“-“, B29) – 1)
Thanks for sharing your formula.
Regards
ExcelDemy