Table of Contents

## 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 **cell 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 all your codes have a correct length.

In our example, a product code **must 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 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 thhis:

=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:

=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 actual length:

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

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