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.

You have a table in Excel and what you want is to combine two columns, row-by-row, into one. For example, you want to merge the First Name & Last Name columns into one, or join several columns such as Street, City, Zip, State into a single "Address" column, separating the values with a comma so that you can print the addresses on envelops later.
Merge Excel columns into one without losing data

Regrettably, Excel does not provide any built-in tool to achieve this. Of course, there is the Merge button ("Merge & Center" etc.), but if you select 2 adjacent cells in order to combine them, as shown in the screenshot:
The Merge button in Excel

You will get the error message "Merging cells only keeps the upper-left cell value, and discards the other values." (Excel 2013) or "The selection contains multiple data values. Merging into one cell will keep the upper-left most data only." (Excel 2010, 2007)

The selection contains multiple data values. Merging into one cell will keep the upper-left most data only.

Further in this article, you will find 3 ways that will let you merge data from several columns into one without losing data, and without using VBA macro. If you are looking for the fastest way, skip the first two, and head over to the 3rd straight away.

Merge two columns using Excel formulas

Say, you have a table with your clients' information and you want to combine two columns (First & Last names) into one (Full Name).

  1. Insert a new column into your table. Place the mouse pointer in the column header (it is column D in our case), right click the mouse and choose "Insert" from the context menu. Let's name the newly added column "Full Name".
    Insert a new column into your table
  2. In cell D2, write the following formula: =CONCATENATE(B2," ",C2)

    B2 and C2 are the addresses of First Name and Last Name, respectively. Note that there is a space between the quotation marks " " in the formula. It is a separator that will be inserted between the merged names, you can use any other symbol as a separator, e.g. a comma.
    Excel formula to merge two columns with spaces

    In a similar fashion, you can join data from several cells into one, using any separator of your choice. For instance, you can combine addresses from 3 columns (Street, City, Zip) into one.
    Formula to merge address from multiple columns in to 1

  3. Copy the formula to all other cells of the Full Name column. For step-by-step instructions please see how to enter the same formula into all selected cells at a time.
  4. Well, we have combined the names from 2 columns in to one, but this is still the formula. If we delete the First name and /or the Last name, the corresponding data in the Full Name column will also be gone.
    Combined names from 2 columns in to 1
  5. Now we need to convert the formula to a value so that we can remove unneeded columns form our Excel worksheet. Select all cells with data in the merged column (select the first cell in the "Full Name" column, and then press Ctrl + Shift + ArrowDown).

    Copy the contents of the column to clipboard (Ctrl + C or Ctrl + Ins, whichever you prefer), then right click on any cell in the same column ("Full Name" ) and select "Paste Special" from the context menu. Select the "Values" radio button and click OK.
    Convert the formula to a value

  6. Remove the "First Name" & "Last Name" columns, which are not needed any longer. Click the column B header, press and hold Ctrl and click the column C header (an alternative way is to select any cell in column B, press Ctrl + Space to select the entire column B, then press Ctrl + Shift + ArrowRight to select the whole column C).

    After that right click on any of the selected columns and choose Delete from the context menu:
    Delete two Excel columns

Fine, we have merged the names from 2 columns into one!
Though, it required quite a lot of effort and time :(
Excel: Merged data from two columns

Combine columns data via Notepad

This way is faster than the previous one, it doesn't require formulas, but it is suitable only for combining adjacent columns and using the same delimiter for all of them.

Here is an example: we want to combine 2 columns with the First Names and Last Names into one

  1. Select both columns we want to merge: click on B1, press Shift + ArrrowRight to select C1, then press Ctrl + Shift + ArrowDown to select all the cells with data in two columns.
    Select 2 columns in Excel that we want to merge
  2. Copy data to clipboard (press Ctrl + C or Ctrl + Ins, whichever you prefer).
  3. Open Notepad: Start-> All Programs -> Accessories -> Notepad.
  4. Insert data from the clipboard to the Notepad (Press Ctrl + V or Shift + Ins)
    Insert data from Excel columns to the Notepad
  5. Copy tab character to clipboard. Press Tab right in Notepad, press Ctrl + Shift + Home, then press Ctrl + X.
  6. Replace Tab characters in Notepad with the separator you need.

    Press Ctrl + H to open the "Replace" dialog box, paste the Tab character from the clipboard in the "Find what" field, type your separator, eg. Space, comma etc. in the "Replace with" field. Press the "Replace All" button; then press "Cancel" to close the dialog box.
    Replace the Tab character with Space

  7. Press Ctr + A to select all the text in Notepad, then press Ctrl + C to copy it to Clipboard.
  8. Switch back to your Excel worksheet (press Alt + Tab), select just B1 cell and paste text from the Clipboard to your table.
    Paste merged data back to the Excel column
  9. Rename column B to "Full Name" and delete the "Last name" column.

There are more steps than in the previous option, but believe me or try it yourself, this way is faster. The next way is even faster and easier :)

Join columns using the Merge Cells add-in for Excel

The quickest and easiest way to combine data from several Excel columns into one is to use Merge Cells add-in for Excel included with our Ultimate Suite for Excel.

With the Merge Cells add-in, you can combine data from several cells using any separator you like (e.g. space, comma, carriage return or line break). You can join values row by row, column by column or merge data from the selected cells into one without losing it.

How to combine two columns in 3 simple steps

  1. Download and install the Ultimate Suite.
  2. Select all cells from 2 or more columns that you want to merge, go to the Datatab > Merge group, and click Merge Cells > Merge Columns into One.
    Select 2 columns to merge and run the Merge Cells add-in
  3. In the Merge Cells dialog box, select the following options:
    • How to merge: columns into one (preselected)
    • Separate values with: choose the desired delimiter (space in our case)
    • Place the results to: left column
  4. Make sure the Clear the contents of selected cells option is ticked and click Merge.
    Select the following options in the Merge Cells dialog box

That's it! A few simple clicks and we've got two columns merged without using any formulas or copy/pasting.

To finish up, rename column B to Full Name and delete column "C", which is not needed any longer.

Much easier than the two previous ways, isn't it? :)

231 responses to "How to merge two columns in Excel without losing data"

  1. ANIL says:


  2. David Bola says:

    This is brilliant!

  3. Liz says:

    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.

    • Alexander says:

      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 and we will try to figure this out.

  4. Jack says:

    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

  5. Ashok Nillay says:

    Sir I am Having data in Excel as shown Below

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

    2 PLOT NO 123

    3 BLD NO 435

    And so on

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

  6. n0s says:

    Incredibly intrusive advertisement. This website added to firewall blacklist

  7. waz says:

    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?

    • HARI says:

      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.

  8. Seyed MJ says:

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

  9. Jay says:

    Excellent !!!

  10. Abdul says:

    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

  11. Mabula says:

    Thank you very much. The formula works very well

  12. JDvideo says:

    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.

  13. Vasilis says:

    Thank you!!!!

  14. Asif says:

    Super Solution........

  15. Anonymous says:


  16. Andy Lo says:

    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.

  17. Haseebullah says:


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

    thank you so much

  18. Jonna says:


    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.


  19. Anupam says:

    very nice,thanks

  20. DD says:

    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

  21. Chris says:


  22. Dr Philip says:

    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

  23. Thokozani says:

    Excellent this works well.

  24. michelle says:

    This worked perfectly - thank you!!

  25. Sara says:

    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.

  26. Julie says:

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

  27. sudhir says:

    Thanks for this hlep...

  28. VINEET says:

    THANKS A LOT... :)

  29. Helena says:

    Brilliant! Thanks!!

  30. Mutisya says:

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

  31. rukhsana says:

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

    • Aksana ( Team) says:

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

  32. Ganesh says:

    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?

  33. Jeff says:

    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?

    • Aksana ( Team) says:

      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:

  34. Jeff says:

    I want to copy and paste THE COMBINED CONCATENATED COLUMN but get a Ref# in each row in the new column. Can you help please?

    • navya says:

      you used concatenated formal used right that your pasting some other place right time you used past special option then select to values that time come to answer sir

  35. Titus says:

    Very Nice

  36. Titus Jesus says:

    Very Nice Thanks....

  37. Sundi says:

    Wow that copy tab in Notepad was clever.

  38. Tim Le says:

    Hello Alex,

    Using the example above with 2 columns "Last Name" and "First Name", if I format all first names in the column "First Name" in RED and BOLD. Can it still retain the BOLD and RED font color of the first name in a new merging cell?

    If that's not possible, then if you know any other methods to advise, I am truly appreciated.

    Thanks Alex.

  39. Triveni M says:

    Superb, Simple if we try to adopt it in our daily task but very useful which results in saving time!!!

  40. Cherry says:

    Thanks a lot.

  41. Pam says:

    Thank you the wonderful fix for my problem!! I am adding you to my Firefox tabs. You made the problem melt away.

  42. new to computers says:


  43. Diana Trinidad says:


  44. vincent holloway says:

    Many Thanks =D

  45. Ruthie Major says:

    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.

  46. kuldeep says:

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

  47. Manjunatha Sindagi says:

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

  48. Danish khan says:

    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.
    150 Ab Executive 27/01/2016
    150 Ab Executive 27/01/2016
    like this type entry should not be accepted showing any already done this entry..

  49. Vamsi says:

    Excellent demonstration. Keep up the good work !

  50. josh says:

    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'

    • Aleksey says:

      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 Please add a link to this article and the number of your comment.

  51. rara says:

    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.

    • Aleksey says:

      Hello, rara

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

  52. Rhylliam says:

    Exactly the info I needed! Thanks!

  53. Munim says:

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

  54. Jim says:

    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!

  55. Anonymous says:

    thank you

  56. Rupali says:

    Thanks ! It Works.

  57. Junny says:

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

  58. Todd says:

    Awesome notepad trick! Thanks meistro

  59. Mike says:

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

  60. Nic says:

    Many thanks. Same as Mike, Brill instructions, thanks

  61. Erica Hooper says:

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

  62. Rob D. says:

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

  63. Alex says:

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

  64. Alex says:

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

  65. dinesh says:

    please suggest 1, 2 3 columns vlooup shortcut

    • HARI says:

      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.

  66. nitin says:

    thank you so much sir ....

  67. HARI says:

    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

  68. Shaukat says:

    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

    • Shaukat says:

      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?

  69. says:

    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

  70. Shreya says:

    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

  71. Treetechnox86 says:

    This is a very helpful tutorial

  72. Arshad says:

    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

  73. Ana Veselinovic says:

    This is a very helpful, thank you!

  74. sandra says:

    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
    Any suggestions please!

  75. April says:

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

  76. mabel says:

    Really helpful

  77. Cynthia says:

    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.

  78. Carmen Holleman says:

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

  79. Izzy says:

    Thank you so much for this! Made my day.

  80. John says:

    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,

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

  81. Jane Soo says:

    Thanks! Really appreciate it!

  82. Linda says:

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

  83. Russ Palmeri says:

    Yep. Worked perfectly. Thanks!

  84. Amith says:

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

  85. urmila seehgal says:

    thanks we got it solved with formula

  86. Rahul Varma says:

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

  87. Domingo Garcia says:

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

  88. Devendra sharma says:

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

  89. Val says:

    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?

  90. Sophia says:

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

    Short and to the point instructions.

    Thank you very much

  91. Lyn says:

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


  92. Adriana says:

    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:


    I need:

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

    please help!!!

    • Hello Adriana,

      Please try the following formula:


      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.

  93. NITIN says:

    Sir I am Having data in Excel as shown Below
    3> RAM KHANA BLD NO 435 MUMBAI-400092

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

    2 PLOT NO 123
    DELHI - 110080

    3 BLD NO 435

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

  94. joydeep says:


  95. Poshetty Are says:


    Thank you

  96. Tona says:

    Thank you for saving the day!


  97. phanuel says:

    Thanks you it is really help ful.



  99. Benhail says:

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

  100. Emily Tolle says:

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