Question

Locked

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

By mdress ·
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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Ah the "general" format

by LarryD4 In reply to How to merge Excel addres ...

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.

Collapse -

Question was answered successfully!

by mdress In reply to How to merge Excel addres ...

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!

Related Discussions

Related Forums