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 10. Total comments: 260

  1. I want to change a date format fro 20001108 to 08/11/2000. I have tried Format Cells but end up with a row of ######. Can any one help?

    1. Hi Jeff,
      I hope you meant ‘November 8, 2000’. If so, the following formula suggested by Nick Mikhuta, a colleague of mine, will bring ‘36838’ (instead of ‘20001108’ in F2) which can easily be transformed into ‘08/11/2000’ via the Excel ‘Format Cells’ menu afterwards:
      =DATEVALUE(CONCATENATE(MID(F2,5,2),"/",RIGHT(F2,2),"/",LEFT(F2,4)))

  2. Wow. its great. but i want to merge the so many sets of column. i can't merge one by one. how can i merge all sets in one time?

  3. sir i am having data shown in excel like
    Robert john
    52 broklyn,
    ny-10001
    24631124
    but i want data look like
    robert john in first column
    52 broklyn,ny-10001 in 2nd column
    24631124 in third column

    1. Hi Rukhsana,
      If I get it right, each of your records consists of four lines, fills a single cell, and has been typed in with three breaks. You can easily change that by applying the ‘Split Text’ and ‘Merge Cells’ tools from ‘Ablebits Ultimate Suite’. Select the column with the original records and opt for ‘Split by Characters’ in the Ablebits ‘Split Text’ drop-down menu. Fix on the ‘Line break’ and ‘Split to columns’ options and click ‘Split’. So, there are four new columns in your table now. Since you need three columns, make use of the ‘Merge Columns into One’ tool (you can find it in the Ablebits ‘Merge Cells’ drop-down menu).

  4. Wonderful!! Seems you have mastered Excel even better than the developers

  5. Brilliant! Thanks!!

  6. THANKS A LOT... :)

  7. Thanks for this hlep...

  8. Such a time saver!! Works beautifully!! Thank you!!

  9. I was really struggling with deleting unwanted columns once I had concatenated information them. This step by step guide was so simple to use and I got the perfect result first time! Thank you.

  10. This worked perfectly - thank you!!

  11. Excellent this works well.

  12. Need some help please. I have 3 separate cells, each ca contain variable data
    e.g. Cells A1 and A2 can contain one of the following P, p, pi, pe,F, f, -, untested, Cell A3 can contain P,p,F,f,-,untested - what I want as a result in A4 is - If A1 and A2 and A3 all contain P or p(i or e also)then ALL OK, IF A1 or A2 or both contain an F or f but A3 = P or p then A4 should read T OK but prob. If Ai and A2 have P,p,pi,pe but A3 is F then A4 should read T OK up to x-point. If A1,A2 and A3 all contain an F or f then A4 should read T F
    Hope you can help - Many Thanks

  13. Thanks!!!!

  14. I have two columns of names with some and different accounts, how can I make it in one. Can you please help me.

    They are last month patients and this month as well in two different spreadsheets. I need to copy the account numbers to the new month for the new arrival patients , but without deleting the old accounts already for the last month and I am tired of copy and pasted because the list in getting bigger and bigger about 300 names combined the old and the new arrivals. How can I "merge" the new names without losing the old one. Please help

  15. very nice,thanks

  16. Alex,

    Quick Questions. i was trying to combine the data from two adjucent columns using your 2nd option(Combine columns data via Notepad). But i stuck up at step 5(5. Copy tab character to clipboard. Press Tab right in Notepad, press Ctrl + Shift + LeftArrow, then press Ctrl + X.).

    I do not understand what character and from where do you want me to copy to clipboard?

    My problem is i was combining user and domain. I have the data like this.
    Column A has name and Column B has domain name. i want to merge both columns adding @ symbol so, i can make it as an email address.
    Please help me. Appreciate your quick response.

    Thanks
    Jonna

  17. Great!!!

    now I know how to combine multiple cells or columns together,

    thank you so much

  18. I need help on collecting data down one column (F1) IF A1 has a number (scale Valve) and once C1 goes true a value of 1 it moves A1 to column F1 but also move previous value down.
    C1 switch between O (off) and 1 (on)

    Application: Excel receive signal from a PLC into a cell. Tag to a cell block and its live. So production is pulling product into a scale Weight 750 lbs. once its collected a valve is open (on command) excel records the weight drop or use.

    Yes a inventory control and if all possible time stamp with the weight entered.
    thanks

  19. correct!!!

  20. Super Solution........

  21. Thank you!!!!

  22. Great formula! However, when I merge the cells (text) it keeps the formula in the cell. When I delete the former individual cells the merged cells also are also deleted.

  23. Thank you very much. The formula works very well

  24. Hello Dear,

    I really faced such problems but could not able to solve and tried a lot to do it but not able to merge both cells with not losing the Data.
    As I Google and found this web site which helped me a lot.

    Once again thank you so much with such useful and important information its really appreciated Dear.

    Best Regards

  25. Excellent !!!

  26. Very useful!
    I do not know if there is a guide or manual for novice like me!

  27. I have various rows with numerous columns of information, I want to copy all these rows into one row, while maintaining the original format. Is that possible?

    1. Hello,copy the first row and paste into clipboard and second row as well then select the row which you want to paste the data.
      go to clipboard and select the paste all option. it will give you the result.

  28. Incredibly intrusive advertisement. This website added to firewall blacklist

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

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

    RAMESH SINGH
    2 PLOT NO 123
    DELHI

    RAM KHANA
    3 BLD NO 435
    MUMBAI

    And so on

    These data should be merged in a single column (Each Address)

    1. If you have 3 columns with data and you want to combine them into 1 column where each cell contains the data from 3 rows, then you can use the Merge Cell add-in:
      https://www.ablebits.com/excel-merge-cells/index.php
      Choose to "merge columns" using the "Carriage return" as the delimiter. If you want to combine data in some other way, please send you workbook at alexander.frolov@ablebits.com and I will do my best to help.

  30. This is a great program and was able to use it on my Excel version 2007 at work.
    However at home I have the 2010 Excel Starter version that is not supported by this add on.
    Is there a chance to create an add on for this program which I would be more than happy to pay for?
    Thank You
    Jack

  31. I typed the formula =CONCATENATE(B2," ",C2) into the cell but what do I do next. When I hit enter nothing happens and when I select columns B and C nothing.

    1. In what cell do you enter the formula? Most likely, the format of that cell is set to text rather than General. Press Ctrl+1 to check this. If the format is correct (i.e. set to General), then you can send us your worksheet to support@ablebits.com and we will try to figure this out.

  32. This is brilliant!

  33. WONDERFUL, THANK YOU

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 :)