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

You may also be interested in

Category: Excel Tips

Table of contents

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

  1. Lita says:

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

    • John says:

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

  2. Brian Owens says:

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

  3. Bilal says:

    thanks a lot for merge formula.

  4. Andrea says:

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

  5. Justin says:

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

  6. trey says:

    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.

    • maxasra says:

      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.

  7. Muhammad Arshad Bhatti says:

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

  8. Phil says:

    Thank you

  9. Roman slepcik says:

    Many thanks.

  10. Lisa says:

    Thank you! Big time saver for me today!

  11. Sandeep Gupta says:

    Merge Tool is really great

  12. Sara Polo says:

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

  13. ML says:

    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.

  14. Taff says:

    Thank you very much , very useful info

  15. Pradnya Ghoderao says:

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

  16. RAJE says:


  17. Auz says:


    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?


    • Doug says:

      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. Phinz says:

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

  19. Gleno says:

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

  20. hari Kishan Ojha says:

    Thankx so much saved my lot of time

  21. Paul Ferrise says:

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

  22. Raanan says:


  23. Ajit Micah says:

    All the suggestion works. Awesome

  24. Manish says:


  25. Kay says:

    Thanks so much, this helped me a lot!

  26. Shubham moonat says:

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

  27. Ajay Kumar says:

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

  28. neeza says:

    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.

  29. arman says:

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

  30. Leesa says:

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

  31. Cal says:

    Information was helpful.

  32. sayali says:


  33. Jaqueline Maritz says:


    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

    • Doug says:

      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?

  34. Melissa says:

    Loved this tutorial! Worked 100%

  35. Shak says:

    100% worked. Thank you. I was able to add comment within formula.

    =CONCATENATE("Trans Date ",A46,", ",C46,", Cheque #", D46)

  36. YOSSI says:

    How to remove your addon from my computer?????????????

  37. Bob L says:

    Very helpful tutorial. It works like a champ and is very helpful. I am using Excel 2016 in Windows 10 and it worked on the first try.

  38. EVAN says:

    I had two columns of names from 2017 and 2018, which I needed to combine. I copied then pasted the second column directly under the first column. I highlighted the entire column then "sorted" the column alphabetically.

    Hope this helped someone.

  39. Thuy says:

    Thanks +++++

  40. Jeff Hauser says:

    Using Office 365, the concatenate formula only worked after I changed the format of the column from "text" to "general".

  41. David says:

    Hi guys,

    would you please help me with merging cells where in one there is a place (city) and in second there is a time? Couldnt figure it our from the text above. Thank you very much.

  42. Caitie says:

    Thanks for taking the time to give this detailed guide!

  43. RT says:

    Thanks so much. Very helpful.

  44. Ayman says:

    very useful

  45. Sam says:

    You can also just click an empty column next to the two you need combined and type. (Let's say A9=First Name and B9=Last Name and the new empty colomn is Fullname)
    =A9&" "&B9
    (The " " is to make a space between the two names.)
    Then hit enter. Select the combined cell and hover over the little box in the right lower corner so the cursor becomes a + and double click it. This will autofill the whole way down the list.
    Click the top of the column you just created for the Fullname to select the whole thing, ctrl+c (or copy), click in a cell right next to it and ctrl+v (or just paste the Value).
    Then you can delete the First Name and Last Name columns.
    Done. (It's kinda like a shorthand/freestyle version of the concatenate function.)

  46. James says:

    you guys rock

  47. Stephen Quirke says:

    OK - that was useful - thanks man

  48. Rich says:

    What a time saver... Thank You, Thank You, Thank You!!!!!!

  49. Harry says:

    I have 1000 names in column A & 1000 in column B. I want to get in colum C in an alternative way. I.e. one data from column A in first row of colum B, then on second row the corresponding data from column B. It should be dynamic.

  50. Johar Hafeez says:

    I want to merge 100 column, just like you merge three column

    writing above formula for 100 or 1000 column is time consuming.

  51. Jacob Msigwa says:

    Thanks very much it has so useful to me. i had a long list to be combined but with the use of this illustration i have succeeded indeed, however i did not go through to the last step.

  52. YOGESH says:

    I want to merge two column as One Bill No. and other as Bill date. Hear as per your above system for merging to single column, date format has changed in value, what is next procedure for making merge from two column in single column for my Bill No. and date

  53. Pradip says:

    very much informative, thank you very much, keep doing the great work

  54. Elizabeth says:

    Thank you so much, it worked and saved me a helluva time

  55. Abdul Kareem says:

    That trick using Notepad is neat :)

  56. Shyamala says:

    Very useful

  57. HEMANTH says:


  58. Denali Marlane says:

    This saved me so much time. Thank you!!!

  59. Mojisola says:

    This just gave additional ranking...Smiles, so happy. Thank you.

  60. Andrew Garcia says:

    Thanks this was helpful!

  61. laxuman says:

    How can convert multiple unequal columns in excel without gap to single column?

  62. Penny P says:

    I did the add on and am following the directions but it is not adding a space, it is adding the wording [Space]. What am I doing incorrectly???

  63. Lakeisha Stockton says:

    Some genuinely interesting info, well written and broadly user genial.


    Superb.. Got valid info

  65. Chu Burrows says:

    If you want to obtain a great deal from this paragraph then you
    have to apply such strategies to your won website.

  66. shivani says:

    I knew about concatenate but i didn't had any idea about how to get the combined result with spaces and then to convert it into a value. Its really very informative. Thanks for sharing.

  67. shivani says:

    I cannot found the ablebits data menu on excel. In which version it can be found?

  68. Randall McLoud says:

    Great - I should certainly pronounce, impressed with your web site.
    I had no trouble navigating through all tabs and
    related info ended up being truly simple to do to access.
    I recently found what I hoped for before you know it at all.
    Quite unusual. Is likely to appreciate it for those who add forums
    or anything, website theme . a tones way for your customer to communicate.

    Excellent task.

  69. Alex says:

    Works Great! Thank you!

  70. poonam says:

    Thank you very much, your formula worked excellent.

  71. Domingo Faunce says:

    It?s hard to come by educated people on this subject,
    however, you seem like you know what you?re talking about!

  72. Karolin Moroney says:

    Some truly interesting information, well written and broadly speaking user friendly.

  73. Shayne Canfield says:

    Wohh precisely what I was searching for, thank you for posting.

  74. Jeannette Smith says:

    Hi I am trying to merge 2 columns. First column contains dates-17/02/19 and second contains times 21:50. When I apply the formula above it just puts a long list of irrelevant numbers

  75. Heath Wrenn says:

    Thank you, I've just been looking for info about this subject for ages and yours is the greatest I have came upon till now.

    However, what in regards to the conclusion? Are you certain in regards to
    the source?

  76. Lavonda Kobayashi says:

    Keep working ,impressive job!

  77. Luis Humffray says:

    I used to be recommended this website by my cousin. I am now not sure whether or
    not this publish is written through him as nobody else know
    such particular about my problem. You are incredible!

  78. Jeanette Grano says:

    If you would like to take a great deal from this article then you
    have to apply such methods to your won blog.

  79. patil says:

    how to add date in this =CONCATENATE(M2, " , L2, ", J2)

  80. Marina Janovska says:

    SUPER!!!! GREAT! It really works! Thank you!!

  81. Webmaster J says:

    Thanks! Super Handy, I will be using this formula in my tool box now.

  82. Ash says:

    This solution was a dream come true for me.
    I am making an English Persian legal dictionary. I had about 22,000 words and wanted to expand on it by translating new legal terminology but was wondering how to add the new terminology so the translated part could also be added with the new word.
    I never thought it was possible. Thank you.

  83. Tshepo says:

    Very Helpful thanks

  84. Adrianne Kimbrell says:

    I used to be suggested this blog via my cousin. I am not positive whether
    this put up is written by means of him as nobody else recognise such
    targeted approximately my difficulty. You're amazing!
    Thank you!

  85. Cathy Tenney says:

    I really like your writing style, great info, appreciate it for putting up :D.

  86. Rajesh Pandey says:

    Your article was really a great help to me as I rarely use excel. Today using it for merging two columns without loosing value was an issue for me. Thanks for really a nice solution.

  87. Emile Weinman says:

    Some genuinely interesting info, well written and loosely user genial.

  88. Meghan McKinnon says:

    I really like your writing style, fantastic info, appreciate
    it for posting :D.

  89. Kalimba says:

    Very instructive tutorial! It helps me and I recommend it to other users!

  90. ANOOP says:


  91. Sasi says:

    Life saver, thank you for much detailed and well informed guidance, greatly appreciated :-)

  92. Simone says:

    select your two columns to combine
    cut and paste without formatting into word doc
    add new empty column
    cut and paste copy from word doc into empty column

  93. Aganyira Dennis says:

    Thanks, indeed i needed this to complete my work

  94. Laurie says:


    This saved me tons of time.

    Just remember to have your column formatted as General and not text.

  95. Will Lehrfeld says:

    Thank You. This was very helpful information and allowed me to resolve an issue i had with combining two sets of data into one.

  96. Fernando says:

    Thank you, I used the CONCATANATE command when I had to merge two or three columns of data together. I haven't had to do that in over three years and I'm not in the office anymore with support. I was going to try a grep command, but forgot that too. Thanks again!

  97. Premila says:

    Very helpful - Thank you very much

  98. hitachi 1.5 ton 3 star split ac says:

    A round of applause for your mind blowing article. Much thanks to you. Fantastic.

  99. KM says:


  100. Karly says:

    This literally saved the day for me. Thank you so much!

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