| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| when i megre cells that begin with zeros , I lose the zeros | Excel Discussion (Misc queries) | |||
| How do I merge address information in Excel into Word? | Excel Discussion (Misc queries) | |||
| How do I show zeros transfered from excel to mail-merge word? | Excel Worksheet Functions | |||
| How do I make an address label in excel, then merge it with Word. | Excel Discussion (Misc queries) | |||
| How come Word is adding zeros to a mail merge number from Excel? | Excel Worksheet Functions | |||
Linear Mode
