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
And if the test is FALSE (the length is correct), we return nothing
That's much better but we can improve the result by showing the length when the test is TRUE