How to merge two columns in Excel without losing data

From this short article you will learn how to merge multiple Excel columns into one without losing data.

You have a table in Excel and what you want is to combine two columns, row-by-row, into one. For example, you want to merge the First Name & Last Name columns into one, or join several columns such as Street, City, Zip, State into a single "Address" column, separating the values with a comma so that you can print the addresses on envelops later.

Regrettably, Excel does not provide any built-in tool to achieve this. Of course, there is the Merge button ("Merge & Center" etc.), but if you select 2 adjacent cells in order to combine them, as shown in the screenshot:

You will get the error message "Merging cells only keeps the upper-left cell value, and discards the other values." (Excel 2013) or "The selection contains multiple data values. Merging into one cell will keep the upper-left most data only." (Excel 2010, 2007)

Further in this article, you will find 3 ways that will let you merge data from several columns into one without losing data, and without using VBA macro. If you are looking for the fastest way, skip the first two, and head over to the 3rd straight away.

Merge two columns using Excel formulas

Say, you have a table with your clients' information and you want to combine two columns (First & Last names) into one (Full Name).

1. Insert a new column into your table. Place the mouse pointer in the column header (it is column D in our case), right click the mouse and choose "Insert" from the context menu. Let's name the newly added column "Full Name".

2. In cell D2, write the following formula: =CONCATENATE(B2," ",C2)

B2 and C2 are the addresses of First Name and Last Name, respectively. Note that there is a space between the quotation marks " " in the formula. It is a separator that will be inserted between the merged names, you can use any other symbol as a separator, e.g. a comma.

In a similar fashion, you can join data from several cells into one, using any separator of your choice. For instance, you can combine addresses from 3 columns (Street, City, Zip) into one.

4. Well, we have combined the names from 2 columns in to one, but this is still the formula. If we delete the First name and /or the Last name, the corresponding data in the Full Name column will also be gone.

5. Now we need to convert the formula to a value so that we can remove unneeded columns form our Excel worksheet. Select all cells with data in the merged column (select the first cell in the "Full Name" column, and then pressCtrl+Shift+ArrowDown).

Copy the contents of the column to clipboard (Ctrl + C or Ctrl + Ins, whichever you prefer), then right click on any cell in the same column ("Full Name" ) and select "Paste Special" from the context menu. Select the "Values" radio button and click OK.

Press Ctrl + H to open the "Replace" dialog box, paste the Tab character from the clipboard in the "Find what" field, type your separator, eg. Space, comma etc. in the "Replace with" field. Press the "Replace All" button; then press "Cancel" to close the dialog box.

7. Press Ctr + A to select all the text in Notepad, then press Ctrl + C to copy it to Clipboard.

Join columns using the Merge Cells add-in for Excel

With the Merge Cells add-in you can combine data from several cells using any separator you like (e.g.: carriage return or line break). You can join values row by row, column by column or merge data from the selected cells into one without losing it.

In what cell do you enter the formula? Most likely, the format of that cell is set to text rather than General. Press Ctrl+1 to check this. If the format is correct (i.e. set to General), then you can send us your worksheet to support@ablebits.com and we will try to figure this out.

This is a great program and was able to use it on my Excel version 2007 at work.
However at home I have the 2010 Excel Starter version that is not supported by this add on.
Is there a chance to create an add on for this program which I would be more than happy to pay for?
Thank You
Jack

If you have 3 columns with data and you want to combine them into 1 column where each cell contains the data from 3 rows, then you can use the Merge Cell add-in:http://www.ablebits.com/excel-merge-cells/index.php
Choose to "merge columns" using the "Carriage return" as the delimiter. If you want to combine data in some other way, please send you workbook at alexander.frolov@ablebits.com and I will do my best to help.

I need help on collecting data down one column (F1) IF A1 has a number (scale Valve) and once C1 goes true a value of 1 it moves A1 to column F1 but also move previous value down.
C1 switch between O (off) and 1 (on)

Application: Excel receive signal from a PLC into a cell. Tag to a cell block and its live. So production is pulling product into a scale Weight 750 lbs. once its collected a valve is open (on command) excel records the weight drop or use.

Yes a inventory control and if all possible time stamp with the weight entered.
thanks

Quick Questions. i was trying to combine the data from two adjucent columns using your 2nd option(Combine columns data via Notepad). But i stuck up at step 5(5. Copy tab character to clipboard. Press Tab right in Notepad, press Ctrl + Shift + LeftArrow, then press Ctrl + X.).

I do not understand what character and from where do you want me to copy to clipboard?

My problem is i was combining user and domain. I have the data like this.
Column A has name and Column B has domain name. i want to merge both columns adding @ symbol so, i can make it as an email address.
Please help me. Appreciate your quick response.

I have two columns of names with some and different accounts, how can I make it in one. Can you please help me.

They are last month patients and this month as well in two different spreadsheets. I need to copy the account numbers to the new month for the new arrival patients , but without deleting the old accounts already for the last month and I am tired of copy and pasted because the list in getting bigger and bigger about 300 names combined the old and the new arrivals. How can I "merge" the new names without losing the old one. Please help

Need some help please. I have 3 separate cells, each ca contain variable data
e.g. Cells A1 and A2 can contain one of the following P, p, pi, pe,F, f, -, untested, Cell A3 can contain P,p,F,f,-,untested - what I want as a result in A4 is - If A1 and A2 and A3 all contain P or p(i or e also)then ALL OK, IF A1 or A2 or both contain an F or f but A3 = P or p then A4 should read T OK but prob. If Ai and A2 have P,p,pi,pe but A3 is F then A4 should read T OK up to x-point. If A1,A2 and A3 all contain an F or f then A4 should read T F
Hope you can help - Many Thanks

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!