Data Validation - Leading Zeros

**RogueArchon** · 03-16-2012, 01:25 PM

Hello!

I'm trying to add data validation to a cell designated for Zip codes. I have no problem getting the data validation to prevent entry of anything but 5 numerical digits...except when the first digit is zero.

I've tried everything I can think of, and I can't get it to recognize that first zero as a number. I even tried formatting the cell with a custom "00000" number format. Currently the validation looks like this:

=AND(ISNUMBER(F24),LEN(F24)=5)

Does anyone know a trick to make Excel recognize the first zero?

Thanks in advance!
RA

**DGagnon** · 03-16-2012, 01:34 PM

try using the data validation requiring a whole number, and set it to less than 100000. then you can format that cell using the zip code format and you should get the results you are looking for.

**DGagnon** · 03-16-2012, 01:38 PM

or since the lowest possible ZIP code is 01001, you could use whole number between 1001 and 99999, with the formating as described above.

**RogueArchon** · 03-16-2012, 01:50 PM

Fantastic! Thank you

I actually tried something like this, but I just set it to a between 0-99999, which naturally would have allowed "123". I didn't even consider checking to see what the lowest zip code is. Cheers!

**DGagnon** · 03-16-2012, 01:53 PM

no problem, glad i could help.

**darknation144** · 03-20-2012, 12:55 PM

Please mark this as solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**alyce** · 05-23-2014, 11:10 AM

I don't know anything about commands to enter for data validation, so can anyone give me a dummy-downed hand with the following? I need to enter 8 digit strings in a column; the first 2 digits and last 6 digits need to be seperated by a dash mark (example: 04-982701 or AA-540120). I was fine until I had to enter a number that started with a zero or a letter. Thanks so much!

**Sam Capricci** · 05-23-2014, 01:19 PM

Alyce, it is against forum rules to post a question on another poster's thread. Feel free to start your own then any replies will be tailored to your needs.