Repost:Converting Zip Codes

[D Collins]

Hello,
I have a program that export data to a csv file, then I
save it as an Excel file. The leading zeroes are
truncated. I have formatted them as zip codes in Excel
and they look fine. Here's the problem: When I import
them into Access (even though they look great in Excel),
the leading zeroes are truncated.

How do I go about getting the zip codes to import into
Access correctly?

I have tried (concatenate("00",a1), but I don't always
need zeroes, as in a zip code such as 34567. Then I tried
to determine the length and have it append the proper
amount of zeroes, but the len function seems to always
return true no matter what length I test for.

I hope this makes sense.

Thanks, D.

 

[Bob Phillips]

Try this

=LEFT("00000",5-LEN(A17))&A17

 

[Dave Peterson]

A couple more:

=right(rept("0",5)&a1,5)
=text(a1,"00000")

 

[bigwheel]

Hi

Although it is possible in Excel to format a cell as Zip Code in Excel, the
contents are still numeric. When imported into Access, the leading zeros
will be removed. Why not try formatting as text, this should then import as
text and retain the leading zeros.

 

[bigwheel]

Hi

Although it is possible to format cells in Excel as Zip Code, the contents
remain as numeric. This will be treated as a number when imported into
Access and will loose the leading zeros. Try formatting as text which
should solve the problem

 

[Bob Phillips]

Isn't that what he was trying to do when concatenating?

 

[D Collins]

Thanks for your input,

But, when the data is exported from the mainframe system,
the leading zeroes are nonexistant. Therefore, I need to
add the zeroes back in. That's where the problem lies.
I could format it as text if I could get that far.

One thing to consider as well, I have zip+4 that I have
to add a zero to and also zip codes that might have all
of their 5 digits, such as 12345-1234 and therefore don't
want to add a zero.

Thanks, D.

 

[D Collins]

Hello,

This didn't seem to work with zip+4, but will work with
zip.

Thanks, D.