On 2009 Jun 8, at 9:15, Blackburn, Melissa Chappars Ms. wrote:
From the university Registrar’s system, I downloaded several
thousand student records into an Excel spreadsheet. The zip codes
look fine in the Excel spreadsheet although the initial zero does
not show in the Formula bar in Excel--the cells are formatted as
zip code.
Farther upstream in your problem was the way Excel treated the zip
codes when you imported them in the 1st place — as numbers. You have
control over the format that Excel will apply to imported data. It's
one of the boxes that appears during the back-and-forth of the
importation dialog. Next time you face this situation, be sure to
specify "Text" instead of either "Number" or "default", and the
incoming lead zeroes will be preserved right from the outset.
Not that you asked, but I personally find it useful to separate the
zip codes into their component parts within FMP — Zip5 and Zip4 — and
to calculate the interposing hyphen if needed. Here's what that
calculation looks like:
Addr Zip Spacer =
Case (
IsEmpty ( Addr Zip5 ); "";
IsEmpty ( Addr Zip4 ); "";
IsEmpty ( Addr Nation ); "-";
Addr Nation = "Canada"; " ";
Addr Nation = "England"; " ";
Addr Nation = "Nederland"; " ";
Addr Nation = "Netherlands"; " ";
"-"
)
This allows me to do validity checking on the length of the Zip5 and
Zip4 components (max of 5 and 4 bytes, respectively) while allowing
me to accommodate Canadian postal codes like "T3A 4A4" and British
ones like "WC1E 7AR".
I also find this one handy:
Addr Zip Prefix =
Case (
IsEmpty ( Addr Zip5 ); "";
Addr Nation = "France"; "F-";
Addr Nation = "Germany"; "D-";
Addr Nation = "Nederland"; "NL-";
Addr Nation = "Netherlands"; "NL-";
Addr Nation = "Norway"; "N-";
Addr Nation = "Spain"; "E-";
Addr Nation = "Sverige"; "S-";
Addr Nation = "Sweden"; "S-";
Addr Nation = "Switzerland"; "CH-";
""
)
