SS leading zeros

This forum is a self-help system for the exchange of ideas among AppleWorks users.

SS leading zeros

Postby jati5335 » Fri Nov 09, 2007 4:26 pm

In transferring address lists from database to spreadsheet, the zip code column drops leading zeros. I am frustrated because many of my addresses require leading zeros (Massachusetts, for example). Please could you tell me how to format the zip code column to print out leading zeros? (Can't find the info in "help" or in manuals!) I need it to work for the whole list automatically, not individually. Thanks!
jati5335
 
Posts: 1
Joined: Fri Nov 09, 2007 4:13 pm
Location: MA

Re: SS leading zeros

Postby Peggy » Fri Nov 09, 2007 8:53 pm

jati5335 wrote:In transferring address lists from database to spreadsheet, the zip code column drops leading zeros. I am frustrated because many of my addresses require leading zeros (Massachusetts, for example). Please could you tell me how to format the zip code column to print out leading zeros? (Can't find the info in "help" or in manuals!) I need it to work for the whole list automatically, not individually. Thanks!


You need to "tell" AppleWorks to treat the zip code as text. If you use the 5+4 zip code format, the hyphen will cause it to be seen as text. If you use just the 5-digit zip code, you can define it as text by using ="zip". You must include the = & the quotes.
Peggy
 
Posts: 767
Joined: Sun Sep 22, 2002 10:50 am
Location: Kent, Washington, USA

Re: SS leading zeros

Postby Barry » Fri Nov 09, 2007 9:21 pm

jati5335 wrote:In transferring address lists from database to spreadsheet, the zip code column drops leading zeros. I am frustrated because many of my addresses require leading zeros (Massachusetts, for example). Please could you tell me how to format the zip code column to print out leading zeros? (Can't find the info in "help" or in manuals!) I need it to work for the whole list automatically, not individually. Thanks!


Hi jati,

This happens because the spreadsheet treats all data entries as numbers (if it can interpret them as numbers), and applies the usual rules of number writing to them, including "No leading zeroes except for numbers less than 1, where a zero is required as a place holder in the ones place—eg: 0.5".

The database keeps the leading zeroes if they are entered into a Text type field. unfortunately, defining a cell as a "Text type" cell is not an available feature in the spreadsheet.

The only option is to somehow convince the spreadsheet that what is going into the cell is Text, not a Number. There are several ways to do this"

Enter the code as a formula: ="00123"
The equals sign tells AppleWorks this is a formula. the double quotes specify that what is between them is Text.

Include one or more non numeric characters in the string of digits.
One or more internal spaces will work. A space appended to either end will be ignored.
Two or more periods will work. One will be interpreted as a decimal.
One or more letters will work, and may be placed anywhere in the string.
Once or more 'special' characters will work, and may be placed anywhere in the string.

The last is probably best. Add an option-space to the end of each Zip code before transferring to the spreadsheet.
Added to the end, the extra character will not affect the sorting order of the codes, nor will it be visible.

To automate the process, add a Calculation type field to your database.

Open the database and show the Layout from which you're doing the transfer.

Go Layout > Define Fields

Name the new field Zip+, set the type to Calculation, then click Create.

Set Display as: to Text

Enter the following in the formula box, using the name of the field containing the unaltered Zip codes in place of 'Zip' if your field has a different name.

'Zip'&" "

If your Zip code field is named Zip, you should be able to copy the formula from here and paste it into the DB. If you are typing it in, note that the space between the pair of double quotes is an option-space. The single quotes are necessary—they tell AppleWorks that what is enclosed is a the name of the field from which it must get data to use in the formula.

After transferring the data to a spreadsheet, you can copy the entire column containing the "fixed" Zip codes then Past it into the column containing the codes that have lost their leading zeroes.

Regards,
Barry
Barry
 
Posts: 1390
Joined: Mon Sep 30, 2002 12:54 am
Location: 48°50' N 123° 30'W


Return to AppleWorks Help

cron
AppleWorks Users Group Logo iWork Users Group Logo