Archive of Mr Excel Message Board
Back to Data in Excel archive index
Back to archive home
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
Re: Data Validation with leading zero.Posted by lenze on October 18, 2001 11:04 AM
Problem SolvedPosted by Marc on October 18, 2001 11:06 AM
I thought I had formatted the field to text, turns out I hadn't. Works fine now.
Re: Data Validation with leading zero.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.
Re: Data Validation with leading zero.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").
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store
to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.