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

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?

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

Replace: & C1
By: & TEXT(C1,"00000")
in the first formula and copy down the column
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LeoLady" wrote in message
...
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?

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

try

=a1& ", " &b1& " " &TEXT(c1,"000000")

"LeoLady" wrote:

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?

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

Thanks, JeremyB! It worked. LeoLady

"JeremyB" wrote:

try

=a1& ", " &b1& " " &TEXT(c1,"000000")

"LeoLady" wrote:

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?

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

Thanks, Bernard Liengme. Your solution worked! Leo Lady

"Bernard Liengme" wrote:

Replace: & C1
By: & TEXT(C1,"00000")
in the first formula and copy down the column
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LeoLady" wrote in message
...
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?