Create test on String Length with LEN

Create test on String Length with LEN
Last Updated on 29/09/2023
Reading time: 3 minutes

Presentation of the LEN function

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

=LEN(string)

The LEN function returns the number of characters in a cells

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.

The LEN function count any characters in the cells

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

Test equal with the function LEN

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

Test with the function LEN

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","")

Function IF with a test based on the length of the ID

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),"")

Returns the number of characters as result

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 😃😍😍

Leave a Reply

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

Create test on String Length with LEN

Reading time: 3 minutes
Last Updated on 29/09/2023

Presentation of the LEN function

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

=LEN(string)

The LEN function returns the number of characters in a cells

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.

The LEN function count any characters in the cells

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

Test equal with the function LEN

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

Test with the function LEN

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","")

Function IF with a test based on the length of the ID

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),"")

Returns the number of characters as result

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 😃😍😍

Leave a Reply

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