Cell format and data validation (SIRET number)
Solved
Finndelle74
Posted messages
4
Status
Membre
-
Phil14 -
Phil14 -
Hello,
I created an Excel file where the user must enter a SIRET number, with a desired display format like 123.123.123.12345
So I set a custom format of 0##.###.###.####0
Furthermore, to ensure that the user does not enter a SIREN number (fewer digits) or make a mistake in the number of digits entered, I implemented data validation: text length equals 14.
This works great... except when the SIRET number starts with a zero! In this case, it does not count the zero in the number of digits and returns the data validation error message. If I put an apostrophe at the beginning, I do not get the format with the dots. And if I combine an apostrophe and spacing dots, I exceed 14 digits...
Does anyone have the miracle solution?
In the meantime, I set the value between 0 and 99999999999999 (14 x "9") which prevents the entry of a 15-digit number, but it remains suboptimal.
Thank you in advance for your replies,
have a nice day.
I created an Excel file where the user must enter a SIRET number, with a desired display format like 123.123.123.12345
So I set a custom format of 0##.###.###.####0
Furthermore, to ensure that the user does not enter a SIREN number (fewer digits) or make a mistake in the number of digits entered, I implemented data validation: text length equals 14.
This works great... except when the SIRET number starts with a zero! In this case, it does not count the zero in the number of digits and returns the data validation error message. If I put an apostrophe at the beginning, I do not get the format with the dots. And if I combine an apostrophe and spacing dots, I exceed 14 digits...
Does anyone have the miracle solution?
In the meantime, I set the value between 0 and 99999999999999 (14 x "9") which prevents the entry of a 15-digit number, but it remains suboptimal.
Thank you in advance for your replies,
have a nice day.
2 réponses
Hello,
to test:
format:
validation:
eric
--
By trying continuously, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to thank you (yes, it can be done!!!), remember to set it as resolved. Thank you
to test:
format:
000"."000"."000"."00000
validation:
=AND(LEN(A2)>11;LEN(TEXT(A2;"00000000000000"))=14)
eric
--
By trying continuously, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to thank you (yes, it can be done!!!), remember to set it as resolved. Thank you
Thank you very much for your reply. Indeed, this is a perfect solution. I now have two "0"s of margin on the front, and there's no risk of having more or less than 14 digits! Great!
It will also work for national registry numbers (11 digits)
Simply replace in the validation ">9" with ">7" and "=14" with "=11"
(+ change the format if you want a hyphen)