by 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. 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 one 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 CONCATENATE formula:
=CONCATENATE(B2," ",C2)In Excel 2016 - Excel 365, you can also use the CONCAT function for the same purpose:
=CONCAT(B2," ",C2)Where 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. Or see how to enter the same formula into multiple cells at once.
- 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 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 did require some effort :)
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 you want to merge: click on B1, press Shift + Right Arrrow to select C1, then press Ctrl + Shift + Down Arrow 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 + Home, 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 included with our Ultimate Suite for Excel.
With the Merge Cells add-in, you can combine data from several cells using any separator you like (e.g. space, comma, 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 3 simple steps
- Download and install the Ultimate Suite.
- Select all cells from 2 or more columns that you want to merge, go to the Ablebits.com Data tab > Merge group, and click Merge Cells > Merge Columns into One.
- In the Merge Cells dialog box, select the following options:
- How to merge: columns into one (preselected)
- Separate values with: choose the desired delimiter (space in our case)
- Place the results to: left column
- Make sure the Clear the contents of selected cells option is ticked and click Merge.
That's it! A few simple clicks and we've got two columns merged without using 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? :)
255 comments
This literally saved the day for me. Thank you so much!
LIFESAVER!!! THANK YOU!
A round of applause for your mind blowing article. Much thanks to you. Fantastic.
Very helpful - Thank you very much
Thank you, I used the CONCATANATE command when I had to merge two or three columns of data together. I haven't had to do that in over three years and I'm not in the office anymore with support. I was going to try a grep command, but forgot that too. Thanks again!
Thank You. This was very helpful information and allowed me to resolve an issue i had with combining two sets of data into one.
THANK YOU,
This saved me tons of time.
Just remember to have your column formatted as General and not text.
Thanks, indeed i needed this to complete my work
OR:
select your two columns to combine
cut and paste without formatting into word doc
add new empty column
cut and paste copy from word doc into empty column
DONE
Life saver, thank you for much detailed and well informed guidance, greatly appreciated :-)
VERY USEFUL...THANK YOU
Very instructive tutorial! It helps me and I recommend it to other users!
I really like your writing style, fantastic info, appreciate
it for posting :D.
Some genuinely interesting info, well written and loosely user genial.
Your article was really a great help to me as I rarely use excel. Today using it for merging two columns without loosing value was an issue for me. Thanks for really a nice solution.
I really like your writing style, great info, appreciate it for putting up :D.
I used to be suggested this blog via my cousin. I am not positive whether
this put up is written by means of him as nobody else recognise such
targeted approximately my difficulty. You're amazing!
Thank you!
Very Helpful thanks
This solution was a dream come true for me.
I am making an English Persian legal dictionary. I had about 22,000 words and wanted to expand on it by translating new legal terminology but was wondering how to add the new terminology so the translated part could also be added with the new word.
I never thought it was possible. Thank you.
Thanks! Super Handy, I will be using this formula in my tool box now.
SUPER!!!! GREAT! It really works! Thank you!!
how to add date in this =CONCATENATE(M2, " , L2, ", J2)
If you would like to take a great deal from this article then you
have to apply such methods to your won blog.
I used to be recommended this website by my cousin. I am now not sure whether or
not this publish is written through him as nobody else know
such particular about my problem. You are incredible!
Thanks!
Keep working ,impressive job!
Thank you, I've just been looking for info about this subject for ages and yours is the greatest I have came upon till now.
However, what in regards to the conclusion? Are you certain in regards to
the source?
Hi I am trying to merge 2 columns. First column contains dates-17/02/19 and second contains times 21:50. When I apply the formula above it just puts a long list of irrelevant numbers
Wohh precisely what I was searching for, thank you for posting.
Some truly interesting information, well written and broadly speaking user friendly.
It?s hard to come by educated people on this subject,
however, you seem like you know what you?re talking about!
Thanks
Thank you very much, your formula worked excellent.
Works Great! Thank you!
Great - I should certainly pronounce, impressed with your web site.
I had no trouble navigating through all tabs and
related info ended up being truly simple to do to access.
I recently found what I hoped for before you know it at all.
Quite unusual. Is likely to appreciate it for those who add forums
or anything, website theme . a tones way for your customer to communicate.
Excellent task.
I cannot found the ablebits data menu on excel. In which version it can be found?
Hi Shivani,
The Ablebits Data tab appears after installing any of our tools. Currently, the Merge Cells add-in is included in our Ultimate Suite for Excel. Please check out the above linked page for full details.
I knew about concatenate but i didn't had any idea about how to get the combined result with spaces and then to convert it into a value. Its really very informative. Thanks for sharing.
If you want to obtain a great deal from this paragraph then you
have to apply such strategies to your won website.
Superb.. Got valid info
Some genuinely interesting info, well written and broadly user genial.
I did the add on and am following the directions but it is not adding a space, it is adding the wording [Space]. What am I doing incorrectly???
How can convert multiple unequal columns in excel without gap to single column?
Thanks this was helpful!
This just gave additional ranking...Smiles, so happy. Thank you.
This saved me so much time. Thank you!!!
THANK YOU LOT FOR GIVING LOT OF INFORMATION
Very useful
That trick using Notepad is neat :)
Thank you so much, it worked and saved me a helluva time
very much informative, thank you very much, keep doing the great work
I want to merge two column as One Bill No. and other as Bill date. Hear as per your above system for merging to single column, date format has changed in value, what is next procedure for making merge from two column in single column for my Bill No. and date
Thanks very much it has so useful to me. i had a long list to be combined but with the use of this illustration i have succeeded indeed, however i did not go through to the last step.