Comments on: How to merge two columns in Excel without losing data

From this short article you will learn how to merge multiple Excel columns into one without losing data. Continue reading →

Comments page 5. Total comments: 260

  1. Hi,

    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 can I change on the spreadsheet in order for this to work? Thanks

    1. Jaqueline:
      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?

  2. thanks.

  3. Information was helpful.

  4. Thanks, Exactly what I've been looking for. Saved me a lot of time.

  5. Simple Trick and Instructions But Can Save Ton's of Value Time....

  6. I just copy the name and address column and paste in Word table with the same number of rows and columns, then merge the cell.

  7. concatenation was superb function
    It saved my 8 hours effort :)

  8. Thank you for your post. It helps me alot. Thanks

  9. Thanks so much, this helped me a lot!

  10. Awesome!!

  11. All the suggestion works. Awesome

  12. Thanks!

  13. 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 great service. Have been spreading the word on your site for sometime now...

  14. Thankx so much saved my lot of time

  15. THANK YOU! The formula tip saved me 6 hours of needless work.

  16. Thanks! Saved me a lot of time! ;-)

  17. Hi,

    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. into one and cells into one?

    Thanks.

    1. Auz:
      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.

  18. THANK YOU FOR YOUR HELP.

  19. Thanks a lot. This trick saved a lot of my time.

  20. Thank you very much , very useful info

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

    1. I had the same dilemma. So I tried this ... When indicating "How to merge:" select "Columns into one", separate values with space, place the results "to left column." It worked.

      1. Can you explain this more thoroughly

  22. Super helpful - saved me tons of time. #1 option was easy. Thank you!!!!!!!

  23. Merge Tool is really great

  24. Thank you! Big time saver for me today!

  25. Many thanks.

  26. Thank you

  27. very useful information you share,
    I like it very much.

  28. you missed a step. How do you automatically change the column numbers? If I want to merge 3,000 cells, it's going to take a couple of days to do.

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

  29. Try this formula : [=A2&" "&B2] , Use the Cells you wish to combine instead of A2 and B2 :)

    1. worked for me thank you ☺

    2. Thank you so much Justin, this worked perfectly!

    3. Thanks this is a great formula.

    4. Thank you!

      Using Excel 2003, I put [=C2&" "&C12] (without the brackets) into cell C13 and it worked like a charm.

      1. What if you want to merge three cells? This formula actually did work for two, which was very exciting but I need three merged.

        1. Heather, If you're using the CONCATENATE formula you only need add a separator, as needed and the specific cell(s):
          Example:
          =CONCATENATE(D2,",",E2)
          Should you want to add other cells:
          =CONCATENATE(D2,",",E2,",",F2)
          if you want to add a space also between the characters then:
          =CONCATENATE(D2,", ",E2)

          Hope this is helpful.

      2. Thanks its supporting in excel 2007 also

      3. Thank you very much, your formula worked excellent.

  30. Saved me hours of cut and paste - thank you!!

  31. thanks a lot for merge formula.

  32. I am utilizing excel 2013. I inserted the formula as shown and the result I get is #NAME?. Any Advice?

    1. U can use ****"" ujghc sg

    2. Brian try typing =Concatenate( and then click on the column you want first , "for spaces between the words", click the second column you want )

  33. Nice tutorial. Might be useful for someone, I'm using Excel 2007 on Windows 10 and the function is CONCATENER(), CONCATENATE() didn't work.

    1. No, CONCATENATE() is correct - you are just using the french version of excel where this has been translated..

  34. Thank you!!

  35. May God continue to bless you for making life more stressfree for us. Wonderful add-on! Keep it up!

  36. ITS REALLY HELPFUL THANKS A LOTS

  37. Thanks you it is really help ful.

  38. Thank you for saving the day!

    Excellent!

  39. Excellent

    Thank you

  40. excellent

  41. Sir I am Having data in Excel as shown Below
    1> ASHOK KUMAR BLOCK C/9 KOLKATA - 700009
    2> RAMESH SINGH PLOT NO 123 DELHI
    3> RAM KHANA BLD NO 435 MUMBAI-400092

    I want data which should look like
    ASHOK KUMAR
    1 BLOCK C/O
    KOLKATA - 700009

    RAMESH SINGH
    2 PLOT NO 123
    DELHI - 110080

    RAM KHANA
    3 BLD NO 435
    MUMBAI-400092

    1. Hello,

      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.

      I'll look into your task and try to help.

  42. I have the following formula to concatenate =CONCATENATE(A2," ",B2) so the result will be as follows: A2 B2 in the same cell.
    Once I have that I need value A2 on top of B2 in the same cell.

    The problem is that i need the concatenation as this:

    A2
    B2

    I need:

    A2 value on top of B2 value in the same cell.

    please help!!!

    1. Hello Adriana,

      Please try the following formula:

      =CONCATENATE(A2,CHAR(10),B2)

      When using line breaks to separate the concatenated values, you must have the "Wrap text" option enabled for the result to display correctly. To do this, press Ctrl + 1 to open the Format Cells dialog, switch to the Alignment tab and check the Wrap text box.

      Hope it will help you.

  43. Thanks so much! Found exactly what I needed to know and it worked! Great instructions, easy to follow and very well explained.

    Excellent!!!

  44. I used this method
    "Merge two columns using Excel formulas"

    Short and to the point instructions.

    Thank you very much

  45. I have rows of email addresses in excel that I want to merge into word so that I can copy and paste into a recipient list for emails, with the semi colon between each one. Any suggestions?
    Thanks

  46. Its really nice to work with formula"CONCATENATE". Thanks a lot

  47. Extremely helpful the Notepad trick - it worked wonders!!
    (and I used WPS instead of Excel, same thing). THANK YOU!!!

  48. It worked.Saves a lot of time. Thank you.

  49. thanks we got it solved with formula

  50. wow. saved much time. Thanks a lot....

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)