How To Merge Columns Without Losing Data In Excel
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.
How to merge columns without losing data in Excel
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 :)
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.
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.
The tutorial demonstrates different techniques to quickly merge two cells in Excel and combine multiple cells row by row or column by column without losing data in Excel 365, Excel 2021, 2019, 2016, 2013, 2010 and lower.
In your Excel worksheets, you may often need to merge two or more cells into one large cell. For example, you may want to combine several cells for a better data presentation or structure. In other cases, there may be too much content to be displayed in one cell, and you decide to merge it with adjacent blank cells.
But is there a way to merge cells in Excel without losing data? Of course there is. And further on in this tutorial, you will find a few solutions that work in all versions of Excel 2016, Excel 2013, Excel 2010 and lower.
Apart from joining all cells in the selected range, this tool can also merge rows and combine columns, you just have to select the corresponding option in the "What to merge" drop-down list.
This is how you can combine two cells in Excel or merge multiple cells without losing data. Hopefully, this information has proved useful for your day-to-day tasks. I thank you for reading and hope to see on our blog next week.
16-1101-EN-S-000-SD-CL2-0003convert pdf into excel get value showing in excel in two row i need this two values together with top row if this possible with merge command or any other option is there
Hi peopleI have a the following work and I need help. I have A column which has several codes "427, 540,etc" random codes like that. Each code has a list of skills associated to it in column B distributed in different cells along several rows on column B. I need to be able to merge all the skills that belongs to one code for example 427 which goes from A:2 to A:20 into one cell in column C without loosing data and then repeat the same process for the rest of the codes in A. so I will have Column A - header : Key Column B Header:Skills Column C: concatenated list of skills by code.A:2 to A:19 (under the "key" header) I have code 427, for code 427 from B:2 to B:19 I have listed skills, on C:2 I need the listed skills from B:2 to B:19 into that cell (C:2). And repeat the process for "key" 540. Note that the number of cells occupied by skills will differ by each code. Also I need using the code below be able no not having to input myself the cell where I want the merged skills, I want them on the same row that I have the New "key"
Set xJoinRange = Application.InputBox(prompt:="Highlight source cells to merge", Type:=8)xSource = 0xSource = xJoinRange.Rows.CountxType = "rows"If xSource = 1 Then xSource = xJoinRange.Columns.Count xType = "columns"End IfSet xDestination = Application.InputBox(prompt:="Highlight destination cell", Type:=8)If xType = "rows" Then temp = xJoinRange.Rows(1).Value For i = 2 To xSource temp = temp & " " & xJoinRange.Rows(i).Value Next iElse temp = xJoinRange.Columns(1).Value For i = 2 To xSource temp = temp & " " & xJoinRange.Columns(i).Value Next iEnd If
And so on. I want to merge the countries but keep the data in each cell. I know I can use the format painter to do this but this makes further updating of the spreadsheet difficult for users who do not know what I have done. Is there a way I can format the cells to fix this problem?
Further to this, when merged, the data cannot all be sorted alphabetically, so at the moment I have to run a macro to unmerge the cells, sort, and then remerge them using a format painter. If there is a way to avoid using the merge button itself, and achieve the same result that would be perfect.
Hello! ma'm!i am facing the problem of splitting the data of such type100.008400,0.12570099.983900,0.13080099.910600,0.14610099.788500,0.17150099.617700,0.20700099.398400,0.252400into two different columns without commas. Is this possible? If yes than how?
I have a one of the columns with sequentially repeated values. I am looking for a shortcut or the easiest way to merge the cells with same values with in the column without impacting any other columns/Rows.
If you merge multiple columns of data in Excel (no matter which Excel version you are using), only the left column of data will be kept, and the data of other columns will be deleted, if you apply the "Merge and Center" command from Home tab on the Alignment group. This tutorial is talking about how to merge or combine different columns of data into one cell or column in Excel.
2. Select the columns you will merge, and press Ctrl + C keys to copy them. Note: You can also click Home > Copy to copy them.
In Cell D2, please enter the formula =A2&B2&C2, and then drag the AutoFill handle down to apply the formula to other cells in the active column. And the columns of data have been merged into one column. See screenshot:
1. After installing Kutools for Excel, please select the columns of data that you want to merge, and then click Kutools > Combine. See screenshot:
2. In the Combine Columns or Rows dialog box, specify the options as follows: (1) Select Combine columns under To combine selected cells according to following options; (2) Specify a separator for the combined data, here I select the Space option; (3) Specify the cell you want to place your combined result; (4) Specify how you want to deal with the combined cells. You can keep or delete contents from those combined cells, and you can also merge those combined cells. See screenshot:
I have an excel spreadsheet made by someone else. Instead of merging 4 columns G, H, I, & J then typing the information, he left the 4 columns separate and typed the information into column G, The information spread through columns H, I, & J.
With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone. 350c69d7ab