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

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?
 
M

Mike Rogers

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
 
J

JMB

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Function to Count Zip Code Digits With Leading Zero 2
Zip Codes Dropping Leading Zero 3
Leading zero from Zip Code not print on Label Merge 6
Printing Leading Zeroes in Excel 1
Convert a 5 digit zip code to a 3 digit zip code 3
How to merge Excel address columns when some zips begin with zeros 4
Zip Code Macro 1
ZIP code to text? 1

Top