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

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

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

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

    Excellent!!!

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

    Short and to the point instructions.

    Thank you very much

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

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

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

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

  9. thanks we got it solved with formula

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

  11. Yep. Worked perfectly. Thanks!

  12. Using this site saved me so much time, I am excited!!! I spent hours on excel trying to merge two fields manually... Thank you so much!!!

  13. Thanks! Really appreciate it!

  14. Hi,
    what about date and time? how am I going to merge it using CONCATENATE?

    Date Time
    7/6/2017 15:17:55
    if I will concatenate it, numbers will appear and if I will change the format to date or time, it will not appear as is.
    thank you,

    1. Hi, John,

      Please note that CONCATENATE requires at least one text string to work properly.
      Supposing that you have your date in A1 and the time in B1, use this formula to concatenate them into the one cell:
      =CONCATENATE(TEXT(A1,"m/d/yyyy")," ",TEXT(B1,"h:mm:ss"))

      Please take a look at this point of our article that explains how to concatenate numbers and dates correctly.

  15. Thank you so much for this! Made my day.

  16. BEAUTIFUL...saved me hours of retyping manually.
    Thank you!!

  17. EXCELLENT. I'm an Excel Guru and have been trying to figure out why Microsoft has not added this years ago. A BIG thank you. You saved me so much time.

  18. Really helpful

  19. Thank you! (For In cell D2, write the following formula: =CONCATENATE(B2," ",C2)

  20. I have an urgent inquiry pls :(
    I need to merge 2 columns in 1 column but not in the same cell... I need them to be in following cells! Meaning if I have a column A (NAME) & column B (address), I need them to be
    Name
    Address
    Name
    Address
    etc...
    Any suggestions please!

    1. Unfortunately, there's no such formula that would merge the data into 2 different cells of one column. Luckily, we have "Create Cards" that can solve your problem. You can see how it works here and even try it for free for a couple of weeks.

  21. This is a very helpful, thank you!

  22. Sir I need a little help please. I have two column, the first column have text "species name (Italicized) with author name (not italicized)" whereas 2nd column have numbers. when I merge both column, the italic species names becomes non italic too. How can I preserve the unique format of each column intact. As there are hundards of rows thus making spp. name italic again one by one taking lot of time.Thanks

  23. This is a very helpful tutorial

  24. In one column I have D.
    ANother column I have 00002

    I want to combine these two

    as D.00002

    I did with =(E5&D5)

    gives me result as D.2, I need D.00002

  25. in one column I have 00002 and one column I have D.

    I want mergee these two columns, I tried It give s me result as D.2, But I want D.00002

    How can I

  26. i want to combine two cell data into one cell with one cell data contain alphanumeric value PMKVY-INVJ- and other one contain numeric value 00000425, how to combine them

    1. i have tried so many methods like Concatnate function, =a1&a2 etc. all the time what i got is PMKVY-INVJ-425, there are no zeroes, what to do?

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

  28. thank you so much sir ....

  29. please suggest 1, 2 3 columns vlooup shortcut

    1. Hello, select three columns and do the vlookup same and enter the vlookup(A1,Table Array,{1,2,3},0)} in last and enter. you will get the data for three columns at a time.

  30. well the final option doesn't seem to work for Mac.

  31. Absolutely amazing! Great explanation! Thank you very much! THis was very helpful.

  32. Awesome work! Microsoft needs to pay you royalties for this one.

  33. This was perfect!! Thank you for the amazing tutorial!

  34. Many thanks. Same as Mike, Brill instructions, thanks

  35. Wow, first time I was ever able to google something like this and get an easy solution that works. Thanks!

  36. Awesome notepad trick! Thanks meistro

  37. Hellow..i have difficulties in excel.
    How do i merge several columns into one ? Not concatenating.

    Example below (the result intended is in column D:
    A B C D (result)
    Apple Apple (Blank) Apple
    Pear (Blank) Pear Pear
    Plum (Blank) (Blank) Plum
    (Blank) Grape Grape Grape
    (Blank) (Blank) Peach Peach

    Kindly need your help...
    Thanks...

    1. Hi Junny,

      You should use the following formula:
      =TRIM(A1 & (IF(AND(NOT(ISBLANK(B1)), B1 <> A1), " " & B1, "")) & (IF(AND(NOT(ISBLANK(C1)), C1 <> A1, C1 <> B1), " " & C1, "")))

  38. Thanks ! It Works.

  39. thank you

  40. The Notepad trick is so simple. It was exactly what I needed to get the job done.

    One comment: I found that if I highlighted and copied the tab between two cells after pasting the data into Notepad, I could then paste that into the Notepad search window's FIND field.

    Thanks for the excellent help!

  41. This article is really help me to solve a problem. Nice

  42. Exactly the info I needed! Thanks!

  43. I need your help.

    I have combined =concatenate(A1," ",A2). but i want "A2" to be bold?

    is this possible? I always send email every day but i only change the A2 part.

    1. Hello, rara

      Sorry, but you can't apply rich text formatting to the formula result.
      Excel doesn't provide such functionality.

  44. not working. I typed in =CONCATENATE(A1,B1) and pressed 'enter' but nothing happened. the text remains the same - it still just says =CONCATENATE(A1,B1) and yes the 'number' of that colum is set to 'general'

    1. Hello, josh,

      To be able to assist you better, please send us a sample table with your data in Excel and the result you want to get. You can email the file to support@ablebits.com. Please add a link to this article and the number of your comment.

  45. Excellent demonstration. Keep up the good work !

  46. i want to know how to track duplicate entry in excel on the basis of two column like emp id and date suppose 101 emp id & 15/07/2016 entered in the sheet if again i entered the same emp id and same date than i want to show the message entry already done. please help me in this regards.
    eg.
    150 Ab Executive 27/01/2016
    150 Ab Executive 27/01/2016
    like this type entry should not be accepted showing any meesage..like already done this entry..

  47. Very nice and easy.
    Made me feel happy.
    Thank you sir

  48. sir i have 10 sheet in one excel file i want to marge all 10 sheet in a new sheet pls let me know

  49. This was helpful. I downloaded the free trial of the Ablebits Data, but I ended up using the formula method, because the Ablebits data, didn't give me the option that I wanted to separate the data once merged into one cell.

  50. Many Thanks =D

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