Mastering Excel’s LEN Function

 

The LEN function calculates the number of characters in a text string.

For example, using “=LEN(‘Excel Magic’)” would yield the result “11”.

This function is essential for data analysis and validation.

LEN Function Details

Available in:	All versions of Microsoft Excel
User Level:	Beginners
Inputs:	A single text string.
Output:	Numeric value representing the number of characters
Wildcards:	Supports “*” and “?”
Case Sensitive:	No, LEN is not case-sensitive

Using LEN With Other Data Types

What if your data isn’t text, numbers, spaces or special characters?

Dates: Excel converts all dates into a number. It is the number of days since 1st January 1900, so Excel ‘sees’ 1st December 2023 as 45261!

This means that LEN return 5 is asked to calculate the length of 1st December 2023. Understanding how data types are handled is taught in our Excel courses, as they can cause a lot of errors.

Currencies: Currency figures in Excel are treated as simple numbers with formatting added. LEN will only count the numbers.

Where To Find The LEN Function

To find the LEN function, go to the top of the ribbon on your screen.

Click Formulas > Text, LEN Function.

Simple Example

Imagine you have a list of names, and you want to know how long each name is.

If “John Doe” is in cell A2, you’d use:

 

This would return “8”, counting both the characters and the space.

Advanced Example

When working with product codes, they usually have a standard prefix before the unique identifier.

For instance, all your products might have codes that start with “PROD-” followed by a unique set of characters.

To find the length of the product code:

Use the LEN function in combination with the subtraction operation to exclude the length of the prefix.

You could also use the SEARCH function to find where the prefix ends, and cut it out that way.

Suppose your product code is in cell A2 and looks like this: “PROD-0012345”.

To calculate the length of the code excluding the prefix “PROD-“, you would use the following formula:

 

This formula works by first calculating the total length of the string in A2, which includes the prefix.

Then, it subtracts the length of the prefix itself.

For example, if A2 contains “PROD-0012345”, the formula will return 7:

Because “0012345” is 7 characters long.

Remember to replace “PROD-” with the prefix used in your product codes!

excel-promo-2

Troubleshooting & Errors

These are the most common error codes you will come up against.

Error 1: #VALUE! Error

Cause: This error pops up if the input isn’t a text string.

Solution: Ensure the input or cell reference contains valid text.

Error 2: Unexpected Count

Cause: If the character count seems off, unseen spaces are probably the culprits.

Solution: Use the TRIM function to remove any extra spaces before applying LEN.

Error: #NAME? Error

Cause: This issue can arise if “LEN” is misspelt or if Excel doesn’t recognize the function.

Solution: Double-check the spelling and review Excel’s language settings.

LEN Function Use Cases

Character Count in Essays: For students or professionals who need to adhere to a specific character count for essays or reports, the LEN function can quickly determine if they’re within the limit.

Username Length: For website administrators or app developers, the LEN function should be used to check if usernames entered by users meet the required length for consistency.

Checking Phone Numbers: For databases with phone numbers, the LEN function can help ensure that all numbers have the correct number of digits, indicating they’re correctly formatted.