Excel's handy LEN function: Making Character Counting a Breeze

What is the LEN Function?

When is this helpful function?

At first glance, counting the number of letters in a cell doesn't really make sense. Unless you're doing logical tests. Indeed, this function is very useful with:

• With a logical test, like with the IF function, Conditional formatting or Data Validation
• As an argument with array function, like the SEQUENCE function

How to use it as a logical test?

Let's say you have a list of contacts with their names and phone numbers. Before calling them one by one, you want to be sure that all the phone numbers are correct.

Write the LEN function

A US phone number has always (3) 3-4 digits

• 3+3+4 = 10
• But we must also include the parenthesis, the space (after the second parenthesis) and the hyphen
• So, 1+3+1+1+3+1+4 = 14 digits

Let's check it!

FIRST IMPORTANT REMARK: The LEN function could absolutely ALL the characters it sees in a cell, from letters to spaces to symbols.

As you can see, most of the results return 14, but not all of them. And visualize which result is different of 14 is not easy. This is why we must convert or formula into a test case.

Convert the formula into a test case

Creating a logical test is not difficult. You just have to add to the previous formula the value expected

Now, it's easy to find the wrong phone number because the test result is FALSE. But we can do better.

• We can filter on column D to keep only the FALSE
• Use the IF function to Customize the result, like =IF(LEN[@Phone Number])<>14,"Wrong phone number","")
• Or highlight with a color the cases where the result is different of 14 with the conditional formatting

LEN function could be use as an array

The new array functions of Excel (UNIQUE, FILTER, SEQUENCE, ...) could work on 1 or many cells. And it's also the case for strings. For instance, we can work on each character of a string to perform different transformations, like

• Extract only the numbers of a cell in Excel