Custom format leading zero

[Judy Ward]

I have a column of IDs. Some are 4-digit numbers, some are 5-digit numbers
and some are a combination of numbers and text (varying lengths). I want to
add a leading zero to the 4-digit numbers.

I was wondering if it makes any difference whether I enter my own custom
format of "0####" or "00000". Both appear to work--I can't see any
difference in the results. I'm wondering if there are any "gotchas".

Later this data is imported to Access and stored as a text field. The
reason I need the leading zero is so that it will sort in ascending order as
a text field.

I would appreciate any words of wisdom on this subject.

Thank you,
Judy

 

[Rick Rothstein]

Off the top of my head... if you use 0#### and your 4-digit number starts
with a zero... that zero will be lost (only a 4-digit number would be
displayed). So, my advice is to use 00000.

 

[Dave Peterson]

You'll see a difference between the two formatting strings if your value is less
than 1000 (3 digits or fewer).

I don't know anything about Access to help.

 

[ShaneDevenshire]

Hi,

Forget the format, when you import it into Access the leading zeros will be
lost!

Suppose your data starts in cell A1, then in B1 enter the following formula:
=IF(ISNUMBER(A1),RIGHT("0000"&A1,5),A1)

Copy it down, when you import into Access you will get a text field and
leading zeros.

 

[Rick Rothstein]

Thank goodness someone who knows Access came along.<g> Here is a shorter
formula that will do the same thing your formula does...

=TEXT(A1,"00000")

 

[Judy Ward]

Thank you very much for this advice. It would have been so frustrating to
add the leading zero in Excel and have Access strip it out. I added this to
my Excel macro that I use to format the data to get it ready for import--and
it's working great!

Thanks again,
Judy