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).
- 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".
- 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.
- Copy the formula to all other cells of the Full Name column. For step-by-step instructions please see how to enter the same formula into all selected cells at a time.
- 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.
- 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 press Ctrl + 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.
- Remove the "First Name" & "Last Name" columns, which are not needed any longer. Click the column B header, press and hold Ctrl and click the column C header (an alternative way is to select any cell in column B, press Ctrl + Space to select the entire column B, then press Ctrl + Shift + ArrowRight to select the whole column C).
After that right click on any of the selected columns and choose Delete from the context menu:
Fine, we have merged the names from 2 columns into one! Though, it required quite a lot of effort and time :(
Combine columns data via Notepad
This way is faster than the previous one, it doesn't require formulas, but it is suitable only for combining adjacent columns and using the same delimiter for all of them.
Here is an example: we want to combine 2 columns with the First Names and Last Names into one
- Select both columns we want to merge: click on B1, press Shift + ArrrowRight to select C1, then press Ctrl + Shift + ArrowDown to select all the cells with data in two columns.
- Copy data to clipboard (press Ctrl + C or Ctrl + Ins, whichever you prefer).
- Open Notepad: Start-> All Programs -> Accessories -> Notepad.
- Insert data from the clipboard to the Notepad (Press Ctrl + V or Shift + Ins)
- Copy tab character to clipboard. Press Tab right in Notepad, press Ctrl + Shift + LeftArrow, then press Ctrl + X.
- Replace Tab characters in Notepad with the separator you need.
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.
- Press Ctr + A to select all the text in Notepad, then press Ctrl + C to copy it to Clipboard.
- Switch back to your Excel worksheet (press Alt + Tab), select just B1 cell and paste text from the Clipboard to your table.
- Rename column B to "Full Name" and delete the "Last name" column.
There are more steps than in the previous option, but believe me or try it yourself, this way is faster. The next way is even faster and easier :)
Join columns using the Merge Cells add-in for Excel
The quickest and easiest way to combine data from several Excel columns into one is to use 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.
How to combine two columns in 4 simple steps
- Download and install Merge Cells for Excel.
- Select all cells from 2 columns that you want to merge, and go to the "Ablebits.com Data" tab. Click the "Merge cells" button to run the add-in.
- Select the following options on the Merge Cells dialog box:
- Merge selected cells: row by row.
- Separate values with: [Space].
- Place the results to: Left cell.
- Tick the "Clear the contents of selected cells" checkbox.
- Now just click the "Merge" button.
Several simple clicks and we've got two columns merged without entering any formulas or copy/pasting.
To finish up, rename column B to "Full Name" and delete column "C", which is not needed any longer.
Much easier than the two previous ways, isn't it? :)
Nice tutorial. Might be useful for someone, I'm using Excel 2007 on Windows 10 and the function is CONCATENER(), CONCATENATE() didn't work.
I am utilizing excel 2013. I inserted the formula as shown and the result I get is #NAME?. Any Advice?
Brian try typing =Concatenate( and then click on the column you want first , "for spaces between the words", click the second column you want )
thanks a lot for merge formula.
Saved me hours of cut and paste - thank you!!
Try this formula : [=A2&" "&B2] , Use the Cells you wish to combine instead of A2 and B2 :)
Thank you!
Using Excel 2003, I put [=C2&" "&C12] (without the brackets) into cell C13 and it worked like a charm.
Thank you very much, your formula worked excellent.
Thanks its supporting in excel 2007 also
Thanks this is a great formula.
Thank you so much Justin, this worked perfectly!
you missed a step. How do you automatically change the column numbers? If I want to merge 3,000 cells, it's going to take a couple of days to do.
Hi Trey,
Drag the formula down- it worked for me. Then i copied and pasted it as value in the other column and then back again.
very useful information you share,
I like it very much.
Thank you
Many thanks.
Thank you! Big time saver for me today!
Merge Tool is really great
Super helpful - saved me tons of time. #1 option was easy. Thank you!!!!!!!
I downloaded the add in and in the merge cells pop up window. How to Merge field does not give me 'row by row' option only 'rows into one' which creates on really long row and not the individual rows.
Thank you very much , very useful info
Thanks a lot. This trick saved a lot of my time.
THANK YOU FOR YOUR HELP.
Hi,
Thanks for this tutorial. I'd like to merge date and time into a row. However, they are in different format so the dates appear to be incorrect after merge. Can you please advise me what's the best way to do this? I downloaded Ablebeit Data but I can't merge row by row. There are only rows into one, col. into one and cells into one?
Thanks.
Auz:
Don't know of any way to format one cell with different time and date formats. Excel uses two different methods to store these types of numbers.
Thanks! Saved me a lot of time! ;-)
THANK YOU! The formula tip saved me 6 hours of needless work.
Thankx so much saved my lot of time
As a release manager, I can't always remember all the formulas and I use excel a lot. It is great to be able to come to this site and get quick references to my needs. Thanks for providing such a great service. Have been spreading the word on your site for sometime now...
Thanks!
All the suggestion works. Awesome
Awesome!!
Thanks so much, this helped me a lot!
Thank you for your post. It helps me alot. Thanks
concatenation was superb function
It saved my 8 hours effort :)
I just copy the name and address column and paste in Word table with the same number of rows and columns, then merge the cell.
Simple Trick and Instructions But Can Save Ton's of Value Time....
Thanks, Exactly what I've been looking for. Saved me a lot of time.
Information was helpful.
thanks.
Hi,
I have done merging of cells using the above formula with the ampersand sign hundreds of times - yet somehow - it does not seem to work with the current spreadsheet I have been provided. Is it due to the fact that it opens in compatibility mode? what can I change on the spreadsheet in order for this to work? Thanks
Jaqueline:
What is the formula you're using? Can you copy and paste it here?
What is the result you're getting with the formula you're using?
Loved this tutorial! Worked 100%
100% worked. Thank you. I was able to add comment within formula.
=CONCATENATE("Trans Date ",A46,", ",C46,", Cheque #", D46)
How to remove your addon from my computer?????????????
Hello,
Thank you for contacting us. I’m really sorry that you are having difficulty of this kind.
Please try to uninstall the Ablebits product via Control Panel -> Programs and Features.
Feel free to contact us if you need any further assistance.
Very helpful tutorial. It works like a champ and is very helpful. I am using Excel 2016 in Windows 10 and it worked on the first try.
Hi,
I had two columns of names from 2017 and 2018, which I needed to combine. I copied then pasted the second column directly under the first column. I highlighted the entire column then "sorted" the column alphabetically.
Hope this helped someone.
Hi Evan,
Thank you for sharing your solution.
Please note that you can combine multiple sheets into one without copying and pasting your data using our Combine Worksheets Wizard. It can help you merge numerous sheets into one workbook, consolidate your data and even collect data from identically named sheets at a glance.
If you are interested, you can install a fully functional trial version of the add-in and see how it works. Here is the direct download link:
https://www.ablebits.com/files/get.php?addin=xl-consolidate-worksheets
Feel free to contact us if you have any questions or need assistance.
Thanks +++++
Using Office 365, the concatenate formula only worked after I changed the format of the column from "text" to "general".
Hi guys,
would you please help me with merging cells where in one there is a place (city) and in second there is a time? Couldnt figure it our from the text above. Thank you very much.
Hi David,
We have a ready-made solution for your task - Merge Cells Wizard for Excel - that allows you to combine several cells into one at a glance.
Feel free to install a fully functional 7-day trial version of the add-in and see if it works as you need. Here are the detailed instructions how to work with the tool.
If you have any questions, don't hesitate to ask.
Thanks for taking the time to give this detailed guide!
Thanks so much. Very helpful.
very useful
thanks.
You can also just click an empty column next to the two you need combined and type. (Let's say A9=First Name and B9=Last Name and the new empty colomn is Fullname)
=A9&" "&B9
(The " " is to make a space between the two names.)
Then hit enter. Select the combined cell and hover over the little box in the right lower corner so the cursor becomes a + and double click it. This will autofill the whole way down the list.
Click the top of the column you just created for the Fullname to select the whole thing, ctrl+c (or copy), click in a cell right next to it and ctrl+v (or just paste the Value).
Then you can delete the First Name and Last Name columns.
Done. (It's kinda like a shorthand/freestyle version of the concatenate function.)
Sam.. you are a GOD!!! Many goats will be sacrificed at your altar in the High Place! Thank you!!
you guys rock
OK - that was useful - thanks man
What a time saver... Thank You, Thank You, Thank You!!!!!!
I have 1000 names in column A & 1000 in column B. I want to get in colum C in an alternative way. I.e. one data from column A in first row of colum B, then on second row the corresponding data from column B. It should be dynamic.