How to merge cells without losing data Excel
From this short article you will learn how to merge multiple Excel columns into one without losing data. Show 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 formulasSay, you have a table with your clients' information and you want to combine two columns (First & Last names) into one (Full Name).
Fine, we have merged the names from 2 columns into one! Though, it did require some effort :) Combine columns data via NotepadThis 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.
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 ExcelThe 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
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? :) You may also be interested in |