Merge two column in an Excel document.

nav2567
nav2567 used Ask the Experts™
on
I have an excel document with a big list in two columns.

I'd like to merge data in column B into column A.

For example:

Column A          Column B
John                    Dole
Mary                   Coleman
........

Result:
Column A
JohnDole
MaryColeman
......

How do I do it?

Please advise.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Systems Engineer
Commented:
Hi,

You can concatenate it on a new column, using this:

="Column A" & "ColumnB"

Tell me if it is an option for you.

Regards.
Pick an unused column, let's say column C.
Assuming your actual data starts in row 2...
Put the formula =A2&B2 in C2
Drag the formula in C2 down to the end of your data.
If the data in column C looks good...
Select from C2 to the bottom of the data in column C, and do a Copy (e.g. Ctrl-C).
Select A2 and Paste Special Values.
The contents of column C should then change, but you can ignore that.
Delete the contents of column C.
Here is a macro code. click on macro and the job will be done.

please find attached an example.

Join-Columns.xlsb
Sub Joincolumn()
Dim c As Long
Dim lrow
lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For c = 2 To lrow
Cells(c, 1) = Cells(c, 1) & Cells(c, 2)
Next c

'If you want to delete the contents of column B  then Remove "'" from below lines.

'Columns("B:B").Select
'Selection.ClearContents

End Sub

Open in new window

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

You can do it as follow by using formula:

1. Enter this formula in Column D: =CONCATENATE(A2," ",B2)
2. Copy the content of D and paste it in "Column A" as value by using paste special.
Hi Yamaafg,

'1. Enter this formula in Column D: =CONCATENATE(A2," ",B2)'
The specifications provided by nav2567 do not include a space between the names, i.e.
   Result:
    Column A
    JohnDole
    MaryColeman

So, unless nav2567 made a mistake in those specifications, your solution would not give him what he wants.  He'd have to do this instead:
  =CONCATENATE(A2,B2)

Besides that, your solution is basically an incomplete version of the one I provided, since the "CONCATENATE" function does basically  the same thing as the (more concise) "&" operator.

Author

Commented:
Thanks, everyone.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial