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

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?

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

MarketStEl

Formate the cells as Text, Place and "Apostrophe" (single quote ' )n the
cell before entering the zip code or...Gotoformat CellSpecialZip Code.

HTH

Mike ogers

"MarketStEl" wrote:

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?

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

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.


"MarketStEl" wrote:

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?