ZIP code to text?

  • Thread starter Thread starter Scott Schaus
  • Start date Start date
S

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)
 
Use = left(TEXT(B2,"00000"),3) for your example and all will work with ZIP
codes entered as numbers.

Steve.

Scott Schaus said:
I need to convert ZIP code to text, keeping the leading zeros in the text. Formatting a cell as ZIP CODE shows the leading
Click to expand...
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:
Click to expand...
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.
 
Back
Top