The LEN function in Excel

The LEN function in Excel
Last Updated on 29/04/2024
Reading time: 2 minutes

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

What is the LEN Function?

What does the LEN function in Excel and how to write it?

  1. Purpose of the LEN function

    The LEN function in Excel simply counts the characters in a cell. It includes letters, numbers, spaces, symbols like hyphens, commas, etc. Basically, anything you can see in the cell.

  2. How many arguments?

    Only one. The cell content.

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:

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.

Which phone number are wrong

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!

The LEN function should return 14

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

Convert the result of the LEN function into a test case

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

Leave a Reply

Your email address will not be published. Required fields are marked *

The LEN function in Excel

Reading time: 2 minutes
Last Updated on 29/04/2024

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

What is the LEN Function?

What does the LEN function in Excel and how to write it?

  1. Purpose of the LEN function

    The LEN function in Excel simply counts the characters in a cell. It includes letters, numbers, spaces, symbols like hyphens, commas, etc. Basically, anything you can see in the cell.

  2. How many arguments?

    Only one. The cell content.

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:

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.

Which phone number are wrong

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!

The LEN function should return 14

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

Convert the result of the LEN function into a test case

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

Leave a Reply

Your email address will not be published. Required fields are marked *