Question Locked

How to merge Excel address columns when some zips begin with zeros

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I can successfully merge 3 columns containing a City, State, and Zip code, using a formula of =a1& ", " &b1& " " &c1), where d1 is the merged column.

However, in the merged column, 5 digit zip codes beginning with zero drop the 1st zero. 9-digit zip codes leading with zero print correctly. However, most of my zip codes are 5-digit ones. Can you help me?

3 replies

by larryd4

Isn't Excel so annoying, I have yet to figure out how to not have it do that. I just gave up and left a column for zip, which was formated for zip.

by mdress

I'm glad to report that I received a reply at another website that solves my problem with the zip codes losing the leading zero. (I posted my dilemma on 4 tech websites today!). The correct formula, which also allows for 2 spaces between the state and zip code is:

=A1&", "&B1&" "&TEXT(C1,"00000")

Thanks to the 2 whom responded to me on this site! Happy Holidays!

This discussion is closed to new replies.