Excel leading-zero ZIP ocdes

[Richard]

I was trying to CONCATENATE the column entries of City, ST and ZIP
into one cell with the appearance of a coherent address, as you would
type it, say, on an envelope or in an address entry line. As you
know, any leading zero(s) will not transfer. I tried creating a
Special number format of 00000--no dice. Of course, I originally
tried the Special format 00000 Zip code, to the same effect (nada).
An awkward solution is to type a capital "O" as the leading digit. It
works, but it is really dorky looking. Here is the solution I found,
which is not much of a hassle, once you've set it up: Instead of
creating one text box of normal width to accommodate a five-digit Zip
code, create five text boxes side-by-side, each one wide enough to
accept one digit. If you create a visible-line frame around the block
of five, you'll never know you're actually dealing with five
blocks--except, of course, when you have to tab four times to fill out
one Zip code. Anyway, it works. The transfer of the Zip code by way
of CONCATENATE works fine; you just have to set it up with
text1&text2&, etc. Yes, you will have to put in effort that should
have come with the purchase price of the software, and that is
annoying. But: IT WORKS! Problem solved.

 

[Tom Stiller]

I was trying to CONCATENATE the column entries of City, ST and ZIP
into one cell with the appearance of a coherent address, as you would
type it, say, on an envelope or in an address entry line. As you
know, any leading zero(s) will not transfer. I tried creating a
Special number format of 00000--no dice. Of course, I originally
tried the Special format 00000 Zip code, to the same effect (nada).
An awkward solution is to type a capital "O" as the leading digit. It
works, but it is really dorky looking. Here is the solution I found,
which is not much of a hassle, once you've set it up: Instead of
creating one text box of normal width to accommodate a five-digit Zip
code, create five text boxes side-by-side, each one wide enough to
accept one digit. If you create a visible-line frame around the block
of five, you'll never know you're actually dealing with five
blocks--except, of course, when you have to tab four times to fill out
one Zip code. Anyway, it works. The transfer of the Zip code by way
of CONCATENATE works fine; you just have to set it up with
text1&text2&, etc. Yes, you will have to put in effort that should
have come with the purchase price of the software, and that is
annoying. But: IT WORKS! Problem solved.

Assuming cells A1, B1, and C1 contain the city, state, and zip code, the
function:
=A1&", " &B1&" "&TEXT(C1,"#00000")
will do what you want while retaining the ease of entry in the zipcode
column.

--
There are 10 kinds of people in the world:
those who understand binary, and those who don't.

Tom Stiller

PGP fingerprint = 5108 DDB2 9761 EDE5 E7E3
7BDA 71ED 6496 99C0 C7CF

 

[Bernard Rey]

I can't see precisely what you are trying to do. But did
you try formatting the cell to TEXT first? Once you have
done so ("Format" > "Cells" > "Text"), anything you type
will be displayed just as is.

A leading single quote ('01234) in a standard cell would
give about the same result. This could be easier a way to
obtain the result you seem to want.

 

[Bob Greenblatt]

I was trying to CONCATENATE the column entries of City, ST and ZIP
into one cell with the appearance of a coherent address, as you would
type it, say, on an envelope or in an address entry line. As you
know, any leading zero(s) will not transfer. I tried creating a
Special number format of 00000--no dice. Of course, I originally
tried the Special format 00000 Zip code, to the same effect (nada).
An awkward solution is to type a capital "O" as the leading digit. It
works, but it is really dorky looking. Here is the solution I found,
which is not much of a hassle, once you've set it up: Instead of
creating one text box of normal width to accommodate a five-digit Zip
code, create five text boxes side-by-side, each one wide enough to
accept one digit. If you create a visible-line frame around the block
of five, you'll never know you're actually dealing with five
blocks--except, of course, when you have to tab four times to fill out
one Zip code. Anyway, it works. The transfer of the Zip code by way
of CONCATENATE works fine; you just have to set it up with
text1&text2&, etc. Yes, you will have to put in effort that should
have come with the purchase price of the software, and that is
annoying. But: IT WORKS! Problem solved.

Here's a MUCH easier and foolproof way:

Suppose city is in cell D1, State is in cell E1, and Zip is in cell F1; and
that the Zip may not appear with leading zeros. Now in Cell G1 you want this
stuff in one string as it would appear in an address line. Simply enter, in
G1, the formula:
=D1&", "&E1&text(F1," 00000")

Then fill this formula down for the number of rows needed.

 

[JE McGimpsey]

I was trying to CONCATENATE the column entries of City, ST and ZIP
into one cell with the appearance of a coherent address, as you would
type it, say, on an envelope or in an address entry line. As you
know, any leading zero(s) will not transfer. I tried creating a
Special number format of 00000--no dice. Of course, I originally
tried the Special format 00000 Zip code, to the same effect (nada).
An awkward solution is to type a capital "O" as the leading digit. It
works, but it is really dorky looking. Here is the solution I found,
which is not much of a hassle, once you've set it up: Instead of
creating one text box of normal width to accommodate a five-digit Zip
code, create five text boxes side-by-side, each one wide enough to
accept one digit. If you create a visible-line frame around the block
of five, you'll never know you're actually dealing with five
blocks--except, of course, when you have to tab four times to fill out
one Zip code. Anyway, it works. The transfer of the Zip code by way
of CONCATENATE works fine; you just have to set it up with
text1&text2&, etc. Yes, you will have to put in effort that should
have come with the purchase price of the software, and that is
annoying. But: IT WORKS! Problem solved.

Another approach would be to use the MultiCat function found at

http://www.mcgimpsey.com/excel/udfs/multicat.html

or use one of the subs at

http://www.mcgimpsey.com/excel/mergedata.html