# How to Use LEN Function in Excel – 7 Examples

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:

`=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 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.

<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!