Zero prefix

**jswarb001** · 07-29-2009, 12:10 PM

Hi I have a list of 5 digit numbers that I want to prefix with a Zero. Eg 12345 should 012345. I have tried Format/Custom and entering 6 numbers and it actually shows the correct format in the sample.However when I go back to the spreadsheet the number in the white box after the formula bar stays as only 5 digits without the zero prefix. Is there a way to fix this please?

thanks

**DonkeyOte** · 07-29-2009, 12:14 PM

Without physically altering the original value you would need to enter a formula in an adjacent cell to convert to Text: =TEXT(A1,"000000")

Can we ask why you need this ? The Custom Format approach would normally suffice.

**jswarb001** · 07-29-2009, 12:26 PM

Hi its because we are in payroll and need to allocate a set number of digits for each person (ie 6 in this case). Ive tried what you said and it works to an extent(ie it shows in excel) but the system doesnt like the leading zero still presumably because it is in text mode rather than number.

**windknife** · 07-29-2009, 12:36 PM

Try this,

=RIGHT("000000" & A1,6)

HTH,
WindKnife

**DonkeyOte** · 07-29-2009, 12:36 PM

OK to try and clarify the situation...

You can't have

000001 as both a literal value and a numeric type simultaneously because 000001 as a number is 1 ... keeping the numeric type all you can do is apply a Custom Format to the cell so as to display the leading zeroes (as you had done previously).

If you want 000001 literally it will have to be a text string.