Works: Converting Numeric ZIP Code Entries to Text Entries (131468)

SUMMARY

If you are using a bar code font with your postal codes, it may be helpful to convert five-digit numeric postal codes to text entries.

Do this by typing the following formula in a new field or column.

=CHOOSE(LENGTH(STRING(<Field Name or Cell Reference>,0)),"","0000", "000","00","0","")&STRING(<Field Name or Cell Reference>,0)

NOTE: Formulas in this article that wrap onto two or more lines should be typed on one line in a single cell or field.

MORE INFORMATION

If you already have a nine-digit postal code, it is probably in text format because Works recognizes the hyphen after the five-digit postal code as a text character.

However, if only some entries are in text format, those entries result in an error or zero value in this formula. If this occurs, use the following ISERR() test to correct the problem:

=IF(ISERR(LENGTH(<Field Name or Cell Reference>)), CHOOSE(LENGTH(STRING(<Field Name or Cell Reference>,0)),"","0000", "000", "00","0","")&STRING(<Field Name or Cell Reference>,0),<Field Name or Cell Reference>)

If you wish to add the extra four ZIP code digits to the results, append one of the following formulas to the either of the two formulas listed above in this article.

To add -0000 to the end of all postal codes (this produces an incorrect result if some zip codes have been typed as five-digit codes and some typed as nine-digit codes), add the following to the formula:
&"-0000"
To append an actual four-digit number from another cell or field, add the following to the formula:
&"-"&CHOOSE(LENGTH(STRING(<Other Cell or field Reference>,0)),"", "000","00","0","")&STRING(<Other Cell Reference>,0)
If the four-digit codes are text, modify this formula to include the ISERR() test for this cell.