Data Validation with leading zero.
Posted by Marc on October 18, 2001 10:59 AM
I have created a request form on a worksheet that collects data. I want to validate that a field(cell) is 3 numbers always. I have set up the data validation to read "text length" "equal to" "3". It works fine when there are no leading zero's, but when a number such as 027 is entered it will not recognize the zero. I know I'm close and I'm sure a macro could do it. I just thought that there is something I'm missing with the data validation. Thanks, Marc
Posted by lenze on October 18, 2001 11:04 AM
Posted by Marc on October 18, 2001 11:06 AM
Problem Solved
I thought I had formatted the field to text, turns out I hadn't. Works fine now.
Posted by lenze on October 18, 2001 11:06 AM
Instead of Data Validation, you might use custom formating to display 3 digits. Choose Format Cells and on the Number tab choose Custom from the category field. Then in the type field, enter 000. This will automatically fill in leading zeroes for you.
Posted by Mark W. on October 18, 2001 11:08 AM
If you want to preserve a leading zero you either
must format the cell as "Text", type an
apostrophe as a prefix (e.g., '027), or enter
a text expression (e.g., ="027").
