Presentation of the LEN function

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

=LEN(string)

And that's all? ? 🤔🤨😲

Yes, that's all! But this function is very useful for logical tests or for conditional formatting

Remarks

If you have a string containing a comma, point, dash, space, etc. the LEN function counts every character.

For instance, in this picture, you have a different way to write the same string (GivenName) and the function LEN returns a different result for each row.

Now, look carefully at the result of cells A2 and A3.

They look the same but the result is different because the string 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 your codes have the correct length.

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

We can use the function 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 logical test 😃

Insert the function in a logical test

We can easily convert the previous formula into a test just by adding the expected result, like this:

=LEN(A2)=12

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

Invert the test

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

We can write is like this:

=LEN(A2)<>12

Or like this:

=NOT(LEN(A2)=12)

Insert a logical test into an IF function

To customize the result of the test, we insert the test into an IF function, like this:

=IF(LEN(A2)<>12,

Now what's happen if the test is TRUE? Well, we just return a message (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 actual length:

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

Our test is now finished and we can see immediately which cell is not correct 😃😍😍