Microsoft KB Archive/131468

= Works: Converting Numeric ZIP Code Entries to Text Entries =

Article ID: 131468

Article Last Modified on 10/6/2003

-

APPLIES TO


 * Microsoft Works 2.0 Standard Edition
 * Microsoft Works 2.0a
 * Microsoft Works 3.0 Standard Edition
 * Microsoft Works 3.0a
 * Microsoft Works 3.0b

-



This article was previously published under Q131468



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(,0)),"","0000", "000","00","0","")&STRING(,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()), CHOOSE(LENGTH(STRING(,0)),"","0000", "000", "00","0","")&STRING(,0),)

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(,0)),"", "000","00","0","")&STRING(,0)

If the four-digit codes are text, modify this formula to include the ISERR test for this cell.</ul>

Additional query words: kbhowto w_works spreadsheet database

Keywords: kbhowto KB131468

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.