Merge Cells without Losing Data [Quick Tip]

Posted on December 7th, 2010 in Learn Excel , VBA Macros - 76 comments

Merge cells without loosing values - ExcelMany 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 !

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.

  1. Merge cells without loosing data - how to in Excel Select all the cells where your data is. (All the cells need to be in one area in one column).
  2. Adjust the column width so that you can fit all contents in one cell. (basically make it wide enough)
  3. Select Home Ribbon > Fill > Justify
  4. Merge cells now.

That is all. 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,

  1. It doesnt work if the selected cells have numbers or formulas
  2. It only works for cells in a single column, if the cells are spread across several columns, justify will not work.
  3. 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.

For eg. here is the JoinAndMerge() macro I have written that works for pretty much all types of data.

Sub JoinAndMerge()
‘joins all the content in selected cells
‘and puts the resulting text in top most cell
‘then merges all cells

Dim outputText As String
Const delim = ” “

On Error Resume Next

For Each cell In Selection
outputText = outputText & cell.Value & delim
Next cell
With Selection
.Clear
.Cells(1).Value = outputText
.Merge
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
End With
End Sub

Download the VBA Code Module

Just copy this code in to a new module or add it your personal macros workbook. And you can use it to quickly merge cells without loosing data. Simple.

The code is easy to figure out if you know basic VBA. So I will not go in to detail.

What to do with all the saved time?

Simple, go read a quick tip.

PS: It is likely that you will save even more time by reading the quick tips, then what?

PPS: Simple, go play.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

76 Responses to “Merge Cells without Losing Data [Quick Tip]”

  1. gerdami says:

    Yes, but with your VBA sub, you end up with a delim which should be trimmed.

  2. Steve Sexton says:

    Unfortunately, the company I work for is using Excel 2003 and the function will not work.

  3. Chandoo says:

    @Gerdami… I know the mistake in code. I left in on as when you concatenate such text, 99% of time you are doing it for cosmetic reasons, so an extra space wouldnt hurt + I wanted to keep the code easy to understand for our readers.

    @Steve… I am not sure if that is the case. I just tested the code in Excel 2003 and it works fine. May be when you copied it, all the quotation marks ‘ ” are replaced by wrong characters?

  4. Godwin says:

    Chandoo, why do you provide screenshots with Excel 2007 and still continue to use Excel 2003 when Excel 2010 is out :)

  5. Godwin says:

    I saw the article about you on msn. It was very inspiring. Thats how I came to know about your blog. You should also check out my blog sometime though I’m just a beginner.
    Thanks.

  6. gerdami says:

    Don’t worry Chandoo, thanks for sharing this.

  7. One more gem from Chandoo! Thanks for sharing it :)

  8. Gregory says:

    Wow, I can’t remember coming across this Fill>Justify tip before. Nice one. I checked Excel 2003, 2007, 2010, and Mac versions 2008, and 2011 to see if it works and it did. Amazing!

    I did have to replace ‘ with ‘ to designate comments, and
    ” “ with ” ” in the Const declaration before my compiler would okay the code.

  9. Sebastien Labonne says:

    I did not of Fill/justify before. I noticed it could also split wrapped up text in a cell in multiple cells.

    It’s much different than setting horizontal text alignment to “Justify” in the format cell dialog.

    Personnaly, assuming the text is in column 1, I would use =A1&” “&A2. Copy the formula down to the last row than copy/paste value the result.

    Sebastien

  10. Kamarrah says:

    This is a wonderful tip! Too bad it only works in Excel 2007! Keep them coming.

  11. Ninad Pradhan says:

    Don’t recollect the Fill->Justify earlier and to have wasted all that time……….grrrrrrrrrrrrrr

  12. PSL says:

    Thanks!

    Face this problem many a times.

    Many of us face ‘another’ problem. It’s actually lose and not loose. Funny how often this mistake is committed.

    cheers,
    PSL

  13. John says:

    Hi Chandoo

    Would you consider covering the general topic of converting 2003 VBA code to 2007/10 in a future newsletter – ie what is the process of converting 2003 macros and situations like this?

    Cheers
    John

  14. Chandoo says:

    @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: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

    @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.

  15. Rick Rothstein (MVP - Excel) says:

    @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?

  16. Chandoo says:

    @Rick… Good modification. I tried to use Join but failed several times. It didnt occur to me that I need to transpose the data. Thanks for sharing it.

    I used the WrapText option so that if the merged text becomes too large, it would wrap nicely inside the cell. I am not sure if without that option the merged content would be visible completely. What do you think?

  17. Steve Sexton says:

    Oops, it does work in 2003

  18. gerdami says:

    Don’t worry Steve, the problem is with the strange quotes ‘ ” “ displayed on this page.

  19. Rick Rothstein (MVP - Excel) says:

    @Chandoo,

    The reason I asked why were you setting the the WrapText property to True was because of this instruction you gave above…

    2. 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…

    Sub JoinAndMerge()
    Const Delimiter = ” ”
    On Error Resume Next
    With Selection
    If Selection.Rows.Count > 1 Then
    .Item(1).Value = Join(WorksheetFunction.Transpose(Selection), Delimiter)
    .Item(2).Resize(Selection.Count – 1).Clear
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    Else
    .Item(1).Value = Join(WorksheetFunction.Index(Selection.Value, 1, 0), Delimiter)
    .Item(2).Resize(1, Selection.Count – 1).Clear
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlGeneral
    End If
    .Merge
    .WrapText = True
    End With
    End Sub

    Note that I still retained the WrapText property setting statement (in both of these routines).

  20. Roopa says:

    Hi Chandoo,
    In your latest comment on merging cells, what are the pros and cons of using concatenate command instead of the VBA?
    Also, I found a relatively simple link to merge text in cells–check this out
    http://www.contextures.com/xlCombine01.html

  21. Kamarrah says:

    I got to work! Yeah Chandoo thanks.

  22. César says:

    Hello Chandoo.

    There is a old trick to do that.

    If you have the data in a1:a5, select b1:b5 and merge the cells, copy b1:b5, select a1:a5 and Paste Special – Format then you have a1:a5 merged but the individual data in a1:a5 still remains there. If you split the cells a1:a5 you see the individual data again, even with the cells merged you can refers one of them individually.

    Kind Regards. César.

  23. Chandru says:

    Hi Chandoo,

    Thank you and I need opposite action to this
    If a cell contains multiople data (abc123, def456) seperated by coma/space needs to be splited into new rows below (a new row should be inserted below and the data should be populated) could you suggest…
    Cheers, Chandru…

  24. Nikki says:

    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

  25. usha SN says:

    Hi chandoo,

    I know one formual to merge the cell in excel woth out losing data, but dnt know how to update here, Pls guide me.
    And thanks a lot beca i am very curious to know abt xcel and here ia m learning so many new things.

  26. Kapil Bhola says:

    Merge Cells without Losing Data :
    I make macros for this :
    Sub lastrow()
    Dim lastrow
    lastrow = Range(“b” & Rows.Count).End(xlUp).Row + 1
    Range(“B” & lastrow) = Range(“A6″) + Range(“A7″)============= Range Can be multiple like range (“A5″), Range(“A8″) Etc.
    End Sub

  27. ush says:

    its very simple to merge cells you can watch it on the next tutrial:

    http://www.thebestdata.com/zoom.aspx?menutype=1&auto=2189


    Merge cells in Excel 2010

  28. Mike Dodds says:

    Here is one for you!! I’m still trying to figure out how to make it work.

    Just take say 400 rows and 14 to 16 narrow columns as if you were making a bar chart, then merge every second column vertically and fill it in with color. Some of the verticals can be split two or three times vertically. The entire page will probably freeze before you get the columns complete.

    Now try and work out a solution the page freeze.

  29. Tim McDonald says:

    Help! I’ve used Fill/Justify for years. Just “upgraded” to Excel 2010 for Windows. Cant find Fill Justify. Help!! Microsoft Help is worthless…

    Thanks!

  30. Prasad says:

    Chandoo, How did you get this ? what made you increase width and select all cells and click fill > justify?? Do you have tie up with Microsoft developers ;)

  31. [...] Merge Several Cells without Loosing Data [macros] Spread some love,It makes you awesome! Tweet [...]

  32. Raghu says:

    Dear Chandoo,
    Many Thanks for all the tips & tricks… i’m learning a lot about excel through this..
    just one quick question:
    how do you show the steps as a gif animation image? do you use any software? if yes, which one? even i’m curious to create some gif animations which i can show in my ppt :)

    thanks a ton mate… & wish you & ur family a very happy diwali… :D

    regards
    raghu

    • Hui... says:

      @Raghu
      The aimated images are Animated GIF files
      I believe that Chandoo uses TechSmith’s, Camtasia Pro screen capture software, although there are a number of screen capture utilities that do the same thing.

  33. Megan says:

    What if I want to merge the cells but keep the paragraph formating so instead of one cell with “big fat cell with lots of text”
    “big
    fat
    cell
    with
    lots
    of
    text”
    Thanks

  34. Hui... says:

    @Megan
    a very simple modification to Chandoo’s code will do the trick
    .

    Sub JoinAndMerge()
    Dim outputText As String
    delim = Chr(10) 'This is the only change
    On Error Resume Next

    For Each cell In Selection
    outputText = outputText & cell.Value & delim
    Next cell
    With Selection
    .Clear
    .Cells(1).Value = outputText
    .Merge
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = True
    End With
    End Sub

  35. Megan says:

    I cannot get that to work. Is ther some kinda code for inserting a new paragraph kinda like using alt enter when your typing in a cell?

  36. Megan says:

    gotta use delim = vbLf

  37. Chris says:

    The macro you provide is great and will save me a lot of frustration, but for some reason it seems to strip all of the formatting from the text (font, font size, font color) in the newly merged cell. Is it possible to modify it so that it maintains the original formatting of the cell?

  38. Riyaz says:

    Hi folks,
    Pls dont worry all about this…
    just omit all the above comments..
    —————–
    Just copy all the cells content whichever you need and paste it in notepad,wordpad,or msword and copy all those data from the wordfile and click inside the required cell in excel and paste it…
    ___________________________
    thats it…. All the best

  39. Bubble says:

    Great help! Keep Rocking (;

  40. Gajesh K R says:

    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

  41. Hui... says:

    Why not just a simple formula
    in Date!A1 put
    `=OFFSET(Sheet1!$A$1,2*ROW()-2,)`

  42. Gajesh K R says:

    thanks for the suggession,
    but im trying it with vba. Can u please help me in this regard

  43. Gajesh K R says:

    hi chandoo,
    this is my new problem, i solved the old one.
    first i want to search for a string in an existing workbook, if it is found then i need to copy the range below it(till the data is present) into a new workbook using VBA.

  44. Trav says:

    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!

  45. Jo Saave says:

    Hi Chandoo,
    Can you tell me how to merge columns without losing the data in the format given below:
    column A column b
    row 1: abcd xyz

    required format:
    column A
    row 1: abcd xyz

    It would save a great amount of my time if i could get a solution to this!

    • Raghu says:

      Hi Jo Saave,
      you can get the data in col a & col b concatenated in col c. if you need to get the merged data back in col a then you may have to copy paste.

      the formula (in col A1) would be =A1&B1″
      if you need the space between char in A1 & B1 then it would be
      =A1&” “&B1″

      hope this helps
      regards
      raghu

  46. Seb says:

    Brilliant!

    • Gokul says:

      The Macro works fine and good once the wrong characters are replaced with the correct quotation marks. Thanks, but when the length of the cell is high then values in the two cells will merge in single line. For this I have move to the end of each value and give alt+ enter to move the second value to the next line. Then how to resolve this?

  47. Mana says:

    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!
     
    Mana

    • Hui... says:

      @Mana
      I definitely would advise not to use Merge

      I would add a new helper/ assistant column called Family
      The add a formula to that to add family as appropriate

       

  48. Satish says:

    Hi All,
    Any body can sujjest a VBA code for this: use logical condition in other words if column 1 with same information in different rows then join the column 3 with all rows can be joined with a comma delimiter. Here is the Example:

    Input
     
     

    No
    Year
    Text

    A-1
    2012
    AB

    A-1
    2012
    CD

    A-1
    2012
    EF

    B-2
    2011
    AB

    B-2
    2011
    CD

    B-2
    2011
    EF

    Output should be
     
     

    No
    Year
    Text

    A-1
    2012
    AB, CD, EF

    B-2
    2011
    AB, CD, EF

  49. yasser says:

    Hi All 
     
    There is a simple and easy way to merge celles without losing the entire data
    =CONCATENATE(cell1,cell2)
     
    thats it 
    hope it’s helpfull

  50. Burak says:

    I was planning to write an email to following addresses:
    sdkjfhds@msn.com
    kjdafhk@gmail.com
    jfh@gmail.com
    jhdfjah@djsldf.com
     
    in excel, then I added A1 & “, ” to B1 and then I noticed here. Result is perfect.
     
    I have never noticed that fill button before :)

  51. whitney says:

    You may have already answered this question, but I am such a newbie to excel I am not sure.  

    I have two columns, A & B.

    Column A is the Family Number. (1k family numbers)
    Column B is the Unique Name.  (50k unique names)

    All the Unique Names in Column B need to be merged into 1 single cell according to the Family Number in Column A.  I tried using “Justify” but it wraps the names onto multiple rows because I can’t make the column wide enough.  

    I am using excel 2007.  

    Thank you!

    Whitney

       

  52. Guy tryin to get work done >.> says:

    i keep getting a syntax error.  and how do i use this?

  53. NaaG says:

    is there any other fuction that can be used that has the same effect but there will be spce between them?

    eg.     cat dog    and not    catdog      

  54. Marco says:

    Nice trick

  55. arvind says:

    Hi sir i just want a clarification from you that im preparing a travel schedule that consist of onward details and return details .. i just sorted the list based on onward date and time and now i just wanted to insert in the records in word using directory technique. everythng is ok but what the problem is im getting the list in order one below the other but i need 1 2 3 in this way not as 
    1
    2
    3
    please help me in this regard 

  56. MUKESH KUMAR says:

    great work.. Thanx.. :)

  57. Issac K Daniel says:

    We can use conconate function….to merge any cells,,,with text

  58. Markus says:

    This is easily possible if the data is in 2 columns. You can use this tool http://www.anotherwaytodothis.com/excel-merge/combine-join-cells.php to merge cells even with data in them.

  59. Madhura says:

    Very Easy One Thanks,,,:)

  60. [...] This code has been written by Chandoo, you read more ways to tackle this issue on his blog here. [...]

  61. Joe says:

    Is it possible to apply this macro to all rows of a 5 x 50 set of data? Sorry, I’m very inexperienced with macros.

  62. sumit kumar Dash says:

    Good.

    The best solution for my need.

    Rgds,
    Sumit

  63. Eric says:

    Hello

    In a sheet where each column has different conditional formatting, is it possible, in VBA, to merge cells vertically without loosing the conditionnal formatting?

    Thank you

Leave a Reply