Need to merge columns

[Jay T]

I have an excell worksheet that comes with the following information in each
column (not rows).

A First Name
B Last Name
C House Number
D Address
E City
F State
G Zip Code
H Area Code
I Phone Number

I need to merge the information in C with D (i.e. 123 Main Street in same
column). I also need to merge information from H with I (504 555 1212)

Help.

 

[DaveB]

In column J (or wherever you have a free column) type this:

=CONCATENATE(C1," ",D1)

and drag down. Then copy this column and paste special->values into column
C or D. Use the same process for column H and I

 

[Duke Carey]

Or:

=C1&" "&D1

In column J (or wherever you have a free column) type this:

=CONCATENATE(C1," ",D1)

and drag down. Then copy this column and paste special->values into column
C or D. Use the same process for column H and I

 

[Jay T]

DaveB,

Thanks but I still don't have the information from the rows appearing in
column J.

 

[DaveB]

That will work as well, essentially that is what the CONCATENATE() function
does behind the scenes.

 

[DaveB]

Well in my formula it is assuming that there is information in C1 and D1. If
not, change the formula to refer to the top of the column where your house
number and address information start and drag that down to the bottom.

Does that help?

 

[Jay T]

It worked for the information in row 1. But I have a list of 600 names,
address etc.

I am clicking on the top of the column and the whole column is highlighted.
Would I need to insert the function =CONCATENATE(C2," ",D2) for row two and
so on for 600 rows OR is there a way I can do the whole column?

 

[DaveB]

Yes in that case you would. Excel has some shortcuts to doing this, however.
Click on the cell where you have the =CONCATENATE(C1," ",D1) function. When
you do this the cell will be highlighted with a bold line around it. In the
bottom right corner of this cell you see a little black square, double click
on this and Excel will fill the ccolumn down to wherever the column right
next to it stops.

For example if you have information in column I, paste that concatenate
function into column J, when you double click the fill option it will fill
column J down to wherever column I stops. Excel will update the formula
respectively for each row changing the C1 and D1 to C2, D2.. C3, D3... ect.

 

[Jay T]

Thank you DaveB.

YOU DA MAN!!!!!

Yes in that case you would. Excel has some shortcuts to doing this, however.
Click on the cell where you have the =CONCATENATE(C1," ",D1) function. When
you do this the cell will be highlighted with a bold line around it. In the
bottom right corner of this cell you see a little black square, double click
on this and Excel will fill the ccolumn down to wherever the column right
next to it stops.

For example if you have information in column I, paste that concatenate
function into column J, when you double click the fill option it will fill
column J down to wherever column I stops. Excel will update the formula
respectively for each row changing the C1 and D1 to C2, D2.. C3, D3... ect.

 

[David R.]

It worked for me! Now how do I get rid of the 2 separate field columns? I
note if I delete them, it affects the new column with the consolidated data.

I hid both columns but I note when I click onto one of the fields in the
consolidated column, it still shows the formula and won't let me edit any
data.

How do I strip formula from all fields on that consolidated column without
losing the data in them?

 

[Gord Dibben]

Select the consolidated column and Copy then in place, Paste
Special>Values>OK>Esc

Delete the original two columns.


Gord Dibben MS Excel MVP