number field won't except zero

I have a cell tha't set up as a number format with the validation only accepting 3 whole number characters, however if the number is 098 then it converts it to 98. I need for that cell to be a number type format and to accept 0 as the first character. I thought I tried everything but I'm sure I'm missing something. Can that be done?

Hi talytech,

Try this

Right Click the Colum or row the numbers are in
In the dropdown list Click on Format Cells
In the Format Cells Box Click on the Numbers Tab
Scroll down to Custom and in the box under Type: enter the amount of 0's you want to appear in front of your number
Click OK

thank you ... but it didn't work. any other suggestions?

Have you tried placing a ' before the 0?

example: '079

I need a formula because the end user is going to fill out the form. This particular field needs to be numeric data type and it needs to allow a zero as a first number. For example what happens now is the user will fill in "078" and after they move to the next field the "078" is converted to "78". How do I keep it from converting to a two digit number?

Why can't you format the cell as text? If you do, then it will alow you to enter information like 079. If you need the info for calculations, there are many ways to convert text to number in a calculated field.

I would do that but I want to keep the user from typing alpha characters in that cell. The cell should require a 3 digit number only, that retains leading zeros and it should not allow any alpha characters. Is that impossible for excel?

Examples:

Value Validation
045 Accepted
445 Accepted
abc Not Accepted because it's alpha characters
4500 Not Accepted because more than 3 digits

Use this code in the data validation CUSTOM formula bar:

Assuming you are validating A1 (change as necessary):

=AND(CODE(A1)>=48,CODE(A1)<=57,LEN(A1)=3)

and format the cell as text.

Let me know if that works

BigBas,

You da bomb man! That worked! Can you explain to me what the formula is doing please. I'd like to be able to understand why it worked.

One more thing ... I want the field to be required. I don't want them to be able to skip the cell without putting something in there. What I did, didn't work but i tried to alter your formula.. this is what I put:

=AND(CODE(D27)>=48,CODE(D27)<=57,CODE(D27)<>"",LEN(D27)=4)