Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to calc | Excel Discussion (Misc queries) | |||
I'm looking for a formula to calc # of days left in a month | Excel Worksheet Functions | |||
Leading zero in zip code won't print | Excel Discussion (Misc queries) | |||
How do I retain leading zero zip code formatting when saving as a. | Excel Discussion (Misc queries) | |||
how do you display a zip code with a leading zero | Excel Discussion (Misc queries) |