ZIP code to text?

[Scott Schaus]

I need to convert ZIP code to text, keeping the leading zeros in the text. Formatting a cell as ZIP CODE shows the leading zero(s), but formulas "see" the value without the zero(s). I have temporarily worked around by entering ZIPs with leading zero(s) as text, using the apostrophe prefix, but this would mean a lot of manual overrides in final project.

Example:
ZIP code in cell B2 is 04986; shows correctly as 04986 with ZIP format in cell, but shows 4986 in formula bar. When I calculate LEFT(B2, 3), result is 498, instead of 049. Currently I manually enter ZIP as '04986, then LEFT(B2,3) yields 049 as expected.

TIA for any thoughts/suggestions.

Scott Schaus
Director of Education
Valley Anesthesia Educational Programs, Inc.
(e-mail address removed)

 

[Steve]

Use = left(TEXT(B2,"00000"),3) for your example and all will work with ZIP
codes entered as numbers.

Steve.

I need to convert ZIP code to text, keeping the leading zeros in the text. Formatting a cell as ZIP CODE shows the leading

zero(s), but formulas "see" the value without the zero(s). I have temporarily worked around by entering ZIPs with leading zero(s) as
text, using the apostrophe prefix, but this would mean a lot of manual overrides in final project.

Example:

ZIP code in cell B2 is 04986; shows correctly as 04986 with ZIP format in cell, but shows 4986 in formula bar. When I calculate
LEFT(B2, 3), result is 498, instead of 049. Currently I manually enter ZIP as '04986, then LEFT(B2,3) yields 049 as expected.