«

»

Function LEN

Presentation of the LEN function

The LEN function returns the number of characters in a cell (LEN for length 😉)

=LEN(string)

And that's all? 🤔🤨😲

Yes that's all! But it is a very useful function for the tests or in conditional formatting 😉

Remarks

If you have a string with coma, point, dash, space, ... the LEN function counts every characters. For instance in this picture, you have different way to write the same string and the function LEN returns different result

Now look carefully at the result of the first and the last cell.

They look the same but the result is different. This is because there is a space at the end of the last cell. You can remove it easily with the TRIM function.

Check if an ID is correct

Let's say you have a list of IDs (customer ID, Product ID, ...). Each ID has the same length and you want to check if all all your codes have a correct length.

In our example, a product code should have 12 digits.

We can use the function LEN like this

=LEN(A2)

 

 

 

But seriously, it's impossible to find in a blink of an eye which cell is correct or not. So let's create a test 😃

Insert the function in a test

We can easily convert the previous formula as a test just by adding the value expected, like this

=LEN(A2)=12

 

 

 

 

Don't be surprised by the 2 = signs. You will find the explanations in this article.

It's better but still not perfect. Let's improve the test

Invert the test

Well in this example, we don't care if the length is correct. What is really interesting is to have the result TRUE when the length is not equal to 12.

We can write is like this

=LEN(A2)<>12

Or like that

=NOT(LEN(A2)=12)

Insert the test in a IF function

To customize the result of the test, we insert the test in an IF function. like this

=IF(LEN(A2)<>12,

Now, what's happen if the test is TRUE, well we just return a message

=IF(LEN(A2)<>12,"Not correct",

And if the test is FALSE (the length is correct), we return nothing

=IF(LEN(A2)<>12,"Not correct","")

That's much better but we can improve the result by showing the length when the test is TRUE

=IF(LEN(A2)<>12,LEN(A2),"")

 

 

 

 

 

Or the gap between the correct length and the current length

=IF(LEN(A2)<>12,LEN(A2)-12,"")

 

 

 

 

Related articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/function-len/


Leave a Reply

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