Merging columns in Excel

SourChipmunk · 24 Oct 2006 at 13:21

I have a worksheet with 3 columns. I would like 1 column to contain all the data from those 3 columns merged together. I have tried the "Merge and Center" button, but it only retains the data in the left-most column.

To explain better, I was given a list of server names, along with a list of domains. I put another column between them with a "." so it should become an FQDN. Unfortunately if I try to just copy/paste this data into notepad it keeps the tabs between the columns.

Anyone know of a solution?

aix0 · 24 Oct 2006 at 13:25

You could add a fourth column and concatenate the data.

edit - I haven't got Excel to hand, but I think the syntax would be something like =concatenate(A1,A2,A3). Not too sure.

madmaxx · 24 Oct 2006 at 13:59

Aix0 is correct, use the concatenate function. Do exactly as he said i.e. =concatenate(A1,B1,C1), and that will join it all up for you in one cell. If you then want to drag the data elsewhere, copy, paste special, values, will fix it as text in the destination cells.
Hope that helps

SourChipmunk · 24 Oct 2006 at 14:02

Thanks, aix0. I'll see if that works next time.

Interim solution: I exported the workbook to a comma delimited text file, then used notepad to replace the commas with nothing. Worked a charm.

SourChipmunk · 24 Oct 2006 at 14:32

aix0 said:
You could add a fourth column and concatenate the data.

madmaxx said:
Aix0 is correct, use the concatenate function.

Gave it a try and Wow! Oh, so much Excel is capable of. That will be extremely handy for revisions. Thank you! :cool:

arcamalpha · 24 Oct 2006 at 16:26

You can also write in cell A4 "=A1 & "." & A2" etc Quite good for adding in extra bits like punctuation.