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. Regrettably, Excel does not provide any built-in tool to achieve this. You will get the error message "Merging cells only keeps the upper-left cell value, and discards the other values.
Merging into one cell will keep the upper-left most data only. 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. 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 Consolidating data into one column in excel as a separator, e.
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, 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.
Now we need to convert the formula to a value so that we can remove unneeded columns form our Excel worksheet. Select the " Values " radio button and click OK. 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: This way is faster than the previous one, it doesn't require formulas, but it is suitable only for combining adjacent columns and using same delimiter for all of them.
Press the " Replace All " button; then press " Cancel " to close the dialog box. 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: 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. You can join values row by row, column by column or merge data from the selected cells into one without losing it.
To finish up, rename column B to "Full Name" and delete column " C ", which is not needed longer. I am utilizing excel How do you automatically Consolidating data into one column in excel the column numbers? If I want to merge 3, 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.
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. 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. 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. 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 service.
Have been spreading the word on your site for sometime now I just copy the name and address column and paste in Word table with the same number of rows and columns, then Consolidating data into one column in excel the cell. 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 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? Feel free to contact us if you need any further assistance.
It works like a champ and is very helpful. I am using Excel in Windows 10 and it worked on the first try. Hi, I had two columns of names from andwhich I needed to combine. I copied pasted the second column directly under the first column.
I highlighted the entire column then "sorted" the column alphabetically. 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
Consolidating data into one column in excel add-in and see how it works. Here is the direct download link: Using Officethe concatenate formula only worked after I changed the format of the column from "text" to "general". Couldnt figure it our from the text above. Thank you very much. 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. This comprehensive set of time-saving tools will help you accomplish any task impeccably without errors or delays. These 8 tools will boost your inbox productivity and simplify your emailing routine. Insert a new column into your table. Place the mouse pointer in the column header it column D in our caseright 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: 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: Replace Tab characters in Notepad with the separator you need. 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.
How to combine two columns in 4 simple steps Download and install Merge Cells for Excel. Select all cells from 2 columns that Consolidating data into one column in excel want to merge, and go to the " Ablebits. Click the " Merge cells " button to run the add-in. Select the following options on the Merge Cells dialog box: Place the results to: Tick the " Clear the contents of selected cells" checkbox.
Now just click the " Merge " button. Much easier than the two previous ways, isn't it? You may also be interested in: January 25, at 5: February 7, at April 18, at 6: February 14, at February 18, at 6: February 24, at June 5, at 1: August 2, at 6: August 9, at October 18, at 6: It's a great tool for combining data when several users work with different You must assign a range name to the columns you're summarizing.
To summarize and report results from separate worksheets, you can consolidate data from each sheet into a master worksheet. The sheets can be in the same. Save your workbook. If this code doesn't do what you want, the only way to go back is to close without saving and reopen.
Select the data you.
MORE: Jon carbone