How can I get a leading zero (ZIP code) to show in a LEFT() calc?

  • Thread starter Thread starter MarketStEl
  • Start date Start date
M

MarketStEl

I have a numeric field that is formatted to display as a ZIP code and a field
to its right that is supposed to show the first three digits of the ZIP code
for 3-digit sorting.

However, Excel strips the leading 0 off ZIP code numbers beginning with it
before storing it, resulting in incorrect data in the field on the right,
which displays the LEFT(...,3) function result. What's more, the actual ZIP
codes print without the leading zeroes.

How can I get the leading zeroes to print and be included in the 3-digit
field?
 
MarketStEl

Formate the cells as Text, Place and "Apostrophe" (single quote ' )n the
cell before entering the zip code or...Goto>format Cell>Special>Zip Code.

HTH

Mike ogers
 
If your first column is formatted as a zip code (Format/Cells/Special/Zip
Code), I'm not sure why it won't print properly.

You could modify your Left Formula:
=LEFT(TEXT(A1,"00000"),3)
to get the leading 0's in your second column.
 
Back
Top