How can I merge 4 excel columns to 1 column (street,city,st,zip
 

I have an excel spreadsheet that has the street address, city, state, and zip
for employees in 4 separate columns. Is there a quick way to merge the 4
columns to 1 while keeping all of the data?

Answer: How can I merge 4 excel columns to 1 column (street,city,st,zip
 

Yes, there is a quick and easy way to merge the 4 columns into 1 while keeping all of the data. Here are the steps:

1. Insert a new column next to the last column that contains data.
2. In the first cell of the new column, enter the following formula:
   Formula:

   ---

   =A1&", "&B1&", "&C1&" "&D1 


   ---

3. Press Enter to apply the formula to the first cell.
4. Double-click the fill handle (the small square at the bottom right corner of the cell) to copy the formula down to the rest of the cells in the column.
5. The new column will now contain the merged data from the original 4 columns.

Here's a breakdown of what the formula does:

- A1, B1, C1, and D1 are the cell references for the first row of data in the original 4 columns.
- The "&" symbol is used to concatenate (join together) the data from each cell.
- The ", " between the ampersands adds a comma and a space between each piece of data.
- The formula ends with D1, which is the last piece of data to be concatenated.

By using this formula, you can quickly and easily merge the data from multiple columns into one column while keeping all of the original data intact.

How can I merge 4 excel columns to 1 column (street,city,st,zip
 

=CONCATENATE(A1," ",B1," ",C1," "D1) and fill down as needed.

Copy, paste special, values to strip the formula and return the concatenated
text string.

Dave
--
Brevity is the soul of wit.


"SassyB" wrote:

I have an excel spreadsheet that has the street address, city, state, and zip
for employees in 4 separate columns. Is there a quick way to merge the 4
columns to 1 while keeping all of the data?

How can I merge 4 excel columns to 1 column (street,city,st,zip
 

Another way is to use this formula, copied down, and set formatting for
Wrap-text.......it will put each item on a separate line in the same cell

=A1&CHAR(10)&B1&CHAR(10)&C1&CHAR(10)&D1

hth
Vaya con Dios,
Chuck, CABGx3



"SassyB" wrote:

I have an excel spreadsheet that has the street address, city, state, and zip
for employees in 4 separate columns. Is there a quick way to merge the 4
columns to 1 while keeping all of the data?

How can I merge 4 excel columns to 1 column (street,city,st,zi
 

What is the CHAR(10) character called? Is that a carriage return?

Dave
--
Brevity is the soul of wit.


"CLR" wrote:

Another way is to use this formula, copied down, and set formatting for
Wrap-text.......it will put each item on a separate line in the same cell

=A1&CHAR(10)&B1&CHAR(10)&C1&CHAR(10)&D1

hth
Vaya con Dios,
Chuck, CABGx3



"SassyB" wrote:

I have an excel spreadsheet that has the street address, city, state, and zip
for employees in 4 separate columns. Is there a quick way to merge the 4
columns to 1 while keeping all of the data?

How can I merge 4 excel columns to 1 column (street,city,st,zi
 

I don't know the Official name, but I call it "Carraige Return", and that's
the way it works for me....just have to be sure and format the cell for
Wrap-text.

Vaya con Dios,
Chuck, CABGx3





"Dave F" wrote:

What is the CHAR(10) character called? Is that a carriage return?

Dave
--
Brevity is the soul of wit.


"CLR" wrote:

Another way is to use this formula, copied down, and set formatting for
Wrap-text.......it will put each item on a separate line in the same cell

=A1&CHAR(10)&B1&CHAR(10)&C1&CHAR(10)&D1

hth
Vaya con Dios,
Chuck, CABGx3



"SassyB" wrote:

I have an excel spreadsheet that has the street address, city, state, and zip
for employees in 4 separate columns. Is there a quick way to merge the 4
columns to 1 while keeping all of the data?

How can I merge 4 excel columns to 1 column (street,city,st,zip
 

Sassy

You may regret it later because if you ever wanted to use the data for a mail
merge or labels or exporting to other applications they would require the data
to be in separate cells.

Having said that...............enter this formula in column E, assuming the 4
columns are A:D

=A1 & " " & B1 & " " & C1 & " " & D1

When happy with results, copy and paste specialvalues then delete columns A:D
if you wish.


Gord Dibben MS Excel MVP

On Wed, 3 Jan 2007 08:41:00 -0800, SassyB
wrote:

I have an excel spreadsheet that has the street address, city, state, and zip
for employees in 4 separate columns. Is there a quick way to merge the 4
columns to 1 while keeping all of the data?

How can I merge 4 excel columns to 1 column (street,city,st,zi
 

Or LineFeed

Dave F wrote:

What is the CHAR(10) character called? Is that a carriage return?

Dave
--
Brevity is the soul of wit.

"CLR" wrote:

Another way is to use this formula, copied down, and set formatting for
Wrap-text.......it will put each item on a separate line in the same cell

=A1&CHAR(10)&B1&CHAR(10)&C1&CHAR(10)&D1

hth
Vaya con Dios,
Chuck, CABGx3



"SassyB" wrote:

I have an excel spreadsheet that has the street address, city, state, and zip
for employees in 4 separate columns. Is there a quick way to merge the 4
columns to 1 while keeping all of the data?

--

Dave Peterson

How can I merge 4 excel columns to 1 column (street,city,st,zi
 

Thread cleanup post

CHAR(10) is LineFeed, CHAR(13) is Carriage return.

Dave F wrote:

What is the CHAR(10) character called? Is that a carriage return?

Dave