Custom format leading zero

  • Thread starter Thread starter Judy Ward
  • Start date Start date
J

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
 
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.
 
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.
 
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.
 
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")
 
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
 
Back
Top