LEN is a short form of LENGTH. The LEN function is used to calculate the length of a text in an Excel cell.
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. To find out whether their names have the same length:
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
Use the TRIM function.
To count the characters ignoring extra 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!
