Function LEN

Presentation of the LEN function

The LEN function returns the number of characters in a cell. It's simple 😉


And that's all? 🤔🤨😲

Yes that's all! But this function is very usefull for the tests or for the conditional formatting


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 cell A2 and A3. They look the same but the result is different of the length is not the same. 🤨🤔

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





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






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

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


Or like that


Insert the test in a IF function

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


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






Or the gap between the correct length and the current length






Related posts

Permanent link to this article:

Leave a Reply

Your email address will not be published.