82 comments Many of us face this problem. We have some data in a few cells. Either for alignment or structure, we would like to merge the cell contents in to one big cell. But Excel wont help as it cannot merge values from all cells. Aparna writes, Hi Chandoo, Can you tell me how to merge rows without losing the data in the format given below. Given data: row 1: abcd row 2: xyz required format: row 1: abcd xyz It would save a great amount of my time if i could get a solution to this!
Important: When you merge multiple cells, the contents of only one cell (the upper-left cell for left-to-right languages, or the upper-right cell for right-to-left languages) appear in the merged cell.The contents of the other cells that you merge are deleted.
![Excel Excel](https://d2d42mpnbqmzj3.cloudfront.net/images/stories/doc-excel/select-merged-cells/xdoc-select-merged-cells-2.png.pagespeed.ic.R-esBNVPUG.png)
That got me thinking, there should be a way to merge cells without losing data?!? Of course there is.
You can use simple features in Excel to do this with ease. Select all the cells where your data is. (All the cells need to be in one area in one column). Adjust the column width so that you can fit all contents in one cell.
![Excel Excel](/uploads/1/2/5/4/125486203/109759530.png)
(basically make it wide enough). Select Home Ribbon Fill Justify. Merge cells now. The text from selected cells will be magically re-arranged in top-most cell. If you see the text spreading 2 rows, just make the column wider and repeat the process.
See this simple animation aside to understand how it works. But wait, this technique has some limitations,. It doesnt work if the selected cells have numbers or formulas.
It only works for cells in a single column, if the cells are spread across several columns, justify will not work. It requires a lot of steps. Shouldn’t there be a better way to merge cells without losing data?!? Of course there is, You can write a simple macro to do this job for you. Here is the JoinAndMerge macro I have written that works for pretty much all types of data. @PSL: Oops, I didnt realize the mistake in spelling.
Fixed it now. Sadly, the url will retain an extra o. @Godwin: Because I have all 3 versions installed on my comp! @Sebastian: I used to the same thing (write =a1&' '&a2 and drag) a while ago. Then I ended up writing a small UDF called as CONCAT that accepts ranges as input and concatenates text in that. It is such a timesaver.
Get it here: @Ninad, Prakash: Thank you. I am happy you like this. @Kamarrah: It works in Excel 2003 too.
@John: I think all macros written in 2003 work in 2007 without any changes in behavior. I may be wrong. I am not an expert in macros, but I will try to put-together an article on what you asked.
@Chandoo, Below is your code modified to remove the loop which concatenates the output text together (note that this method does not produce a trailing delimiter in the output string like your code does). Sub JoinAndMerge ' Joins all the content in selected cells ' and puts the resulting text in top most cell ' then merges all cells Const Delimiter = ' ' On Error Resume Next With Selection.Item(1).Value = Join(WorksheetFunction.Transpose(Selection), Delimiter).Item(2).Resize(Selection.Count - 1).Clear.Merge.HorizontalAlignment = xlGeneral.VerticalAlignment = xlCenter.WrapText = True End With End Sub I have a question though. I left it in (because you included it), but why are you setting the WrapText property to True? @Chandoo, The reason I asked why were you setting the the WrapText property to True was because of this instruction you gave above. Adjust the column width so that you can fit all contents in one cell. (basically make it wide enough) If this instruction is followed, then there would be no need to wrap the text. By the way, we can modify this code to handle merging across a single row instead of down a column.
Sub JoinAndMerge Const Delimiter = ' ' On Error Resume Next With Selection.Item(1).Value = Join(WorksheetFunction.Index(Selection.Value, 1, 0), Delimiter).Item(2).Resize(1, Selection.Count - 1).Clear.Merge.HorizontalAlignment = xlCenter.VerticalAlignment = xlGeneral.WrapText = True End With End Sub And, if we want to generalize the code to handle either a selection down a column or across a row automatically, then this code will do that. This is the macro I use. It accounts for columns and rows and will work with normal formulas.
Selection must be contiguous. (but that's a given, since we're merging the cells) Sub MergeCells Dim result As String For Each cell In Selection.Cells If Not cell.Value = vbNullString Then result = result & Trim(cell.Value) & ' ' End If Next Application.CutCopyMode = False With Selection.Clear.HorizontalAlignment = xlLeft.VerticalAlignment = xlTop.WrapText = True.MergeCells = True End With Selection.Cells(1, 1).Value = result End Sub.
Hi chandoo, I have doubt in excel VBA macro code. Can u help me with it.My problem is: I have multiple vertical cells with values in alternate (leaving 1 cell gap between 2 values)positions eg: Date A1: 02/Nov/2011 A2: A3: 04/Oct/2011 A4: A5: 12/Oct/2011 A6: A7: 25/May/2011 21/Oct/2011 now please let me know how do I copy it to the other workbook vertically(continuously) without a gap of 1 cell inbetween 2 values. The result is supposed to be this: Date A1: 02/Nov/2011 A2: 04/Oct/2011 A3: 12/Oct/2011 A4: 25/May/2011 A5: 21/Oct/2011. Hi, I have a question related to this thread. I have a need to merge columns of data into one cell, with no data loss, but need two additional features: first is to comma seprate the contents of each of the merged cells once they are in the merged cell. Second is to do this for individual rows, but whilst selecting multiplw rows - I mean only merge per row into one cell.
For example, i want to be able to run the macro by selecting all rows in my worksheet, but have columns merged per row, not all rows and columns merged into one cell in teh top left of the sheet. Ie I want a finished sheet of one column with the same number of rows but the columns from each row meged into the first cell of each row. The closest i have come is with a previous post: 1. Nikki says: December 20, 2010 at 8:36 pm This is the macro I use. It accounts for columns and rows and will work with normal formulas. Selection must be contiguous. (but that’s a given, since we’re merging the cells) Sub MergeCells Dim result As String For Each cell In Selection.Cells If Not cell.Value = vbNullString Then result = result & Trim(cell.Value) & ” ” End If Next Application.CutCopyMode = False With Selection.Clear.HorizontalAlignment = xlLeft.VerticalAlignment = xlTop.WrapText = True.MergeCells = True End With Selection.Cells(1, 1).Value = result End Sub this macro merges all selected columns and rows into the one cell, I want to be able to select multiple columns and rows, but only have row by row merged.
Additionally, ideally as the merge is completed i would like to insert a comma between each of the merged cells contents, once it is merged. Hope I have explained this ok?
Any help is much appreciated! Hi Chandoo - Thanks for this! Quick question: I have one column with a list of about 1000 names. Each row is a different name but some rows belong to one family. I am trying to separate each family. In order to do this, I am using your JoinAndMerge macro. Essentially, I am merging the rows that belong to one family so that they become one cell.
I will then use this and use Avery wizard (is that the easiest way to do it?) to print off the names on a avery sticker sheet. However, when I merge the rows of names, I still need them to be in separate lines. I could do it manually with the char(10) function, but I imagine I could edit your macro a little. What would you advise here? Thanks a lot! Hi thank you very much for the macro, that is a gem!!! I wanted to know if instead of using selection but if i would like to add a preset range, how do i rewrite the code for this?
Instead of 'For Each cell In Selection' i would like to merge data in a predetermined cell that will not moved. A1: Apple A2: orange A3: banana A4: Chocolate A5: Coffee A6: Tea A7: Red A8: Pink A9: yellow to become: apple orange banana. Hello Chandoo, this macro works great for my spreadsheet needs, however, a column with several ranges +5,000 of rows that are needed to apply this VBA takes a lot of time and effort, so deciding to use a colored and alternated background for each range in order to visualize which range needs to be joined and merged easily, is there a way on the VBA to grab each range with the same background to run the VBA and continue with the next range on the different background color until it runs to the end of the last row/range? I have conditional formatting relying on a date where the cell is merged because I had to add another row in order to have the sub contractors listed separately.
For example, I have rows 2 and 3 for a task and columns A, B, C, D, E, F, G, H, I, J, K, L, M, N, O and P are all merged however once you get to column Q, R, S, T, U they are no longer merged to show the distinction between the 2 different sub contractors on the task. Column V is merged again. My problem is Column I which is the expiration date of the task is the condition to turn all the cells to the color requested. Because column I is merged the first subcontractor in Row 2, Column Q, R, S, T, U will turn the color but the second sub contractor in Row 3, Column Q, R, S, T and U will not turn the color requested. It will only happen if I don't merge the Column I which has the expiration date and I put the date in both cells (2 and 3). PLEASE HELP IVE BEEN WORKING ON THIS FOR 3 DAYS!!!!!!