Combine two columns in excel without losing data

Excel Formula: Combine cells with comma

Here this tutorial provides some formulas to join multiple cells into one cell with comma as separator as below screenshot shown, and also explains how the formulas work in Excel.

Excel Column Merge We can merge values in two or more cells in excel. We can apply the same for columns as well. All that we require is to apply the formula in every cell of the column by dragging or copy-paste formula in each cell of the column. In order to concatenate two or more columns in Excel, you just enter a usual concatenation formula in the first cell, and then copy it down to other cells by dragging the fill handle (the small square that appears in the lower right hand corner of the selected cell).

Formula 1 Combine cells with comma by using &

Generic formula:

  1. Choose both columns you need to merge: Click B1 press Shift + ArrrowRight for choosing C1 then hit Ctrl + Shift + ArrowDown for choosing entire data cells with data in two columns. And copy data to clipboard open Notepad insert data from the clipboard to the Notepad.
  2. If you merge multiple columns of data in Excel (no matter which Excel version you are using), only the left column of data will be kept, and the data of other columns will be deleted, if you apply the ' Merge and Center ' command from Home tab on the Alignment group.
text_1&','&text_2&','&…&',' &text_n

Arguments

Text_1, text_2, text_n: the cells or text strings you want to combine with comma.

How this formula work

For instance, you want to combine the cells of each row across column B, column C, column D and column E into one cell with commas, please use below formula in a blank cell.

=B3&','&C3&','&D3&','&E3

Press Enter key, then the cells are combined with comma separated.

Explanation

& : using this connector to combine cells and commas.

Remark

If you want to directly use texts instead of cell references in the formula, please embrace the text strings with double quotation marks.

Formula 2 Combine cells with comma by using CONCATENATE function

Generic formula:

CONCATENATE(text_1,',',text_2,',',…',',text_n)

Arguments

Text_1, text_2, text_n: the cells or text strings you want to combine with comma.

Combine Two Columns In Excel With Dash

How this formula work

For instance, you want to combine the cell B3, C3, D3 and E3 into one cell with commas, please use below formula in a blank cell.

=CONCATENATE(B3,',',C3,',',D3,',',E3)

Press Enter key, then the cells are combined with comma separated.

Explanation

CONCATENATE function: CONCATENATE function is used to join text together.

Remark

If you want to directly use texts instead of cell references in the formula, please enclose the text strings with double quotation marks.

If there are blank cells which are used to be combined, there will be extra comma in the combined cell by using above 2 formulas (& and CONCATENATE) as below screenshot shown.

In this case, please choose formula 3.

Formula 3 Combine cells with comma by combining SUBSTITUTE and TRIM functions

Generic formula:

=SUBSTITUTE(TRIM(text_1&' '&text_2&' '&…&' '&text_n),' ',', ')

Arguments

Text_1, text_2, text_n: the cells or text strings you want to combine with comma.

How this formula work

For instance, you want to combine the cell B3, C3, D3 and E3 into one cell with commas, please use below formula in a blank cell.

=SUBSTITUTE(TRIM(B3&' '&C3&' '&D3&' '&E3),' ',', ')

Press Enter key, then drag the fill handle down to apply this formula to the cells you need to combine, then the cells are combined with comma separated.

Combine two columns in excel without losing data

Explanation

TRIM function: the TRIM function removes all extra spaces from the text string. Here TRIM(B3&' '&C3&' '&D3&' '&E3) will combine cells with spaces and then clear the extra spaces, this formula will stop the trailing or leading comma.

SUBSTITUTE function: the SUBSTITUTE function is used to replace text with the specific text. Here =SUBSTITUTE(TRIM(B3&' '&C3&' '&D3&' '&E3),' ',', ') will replace space with comma and space in the combined text string (the combination of B3, C3, D3 and E3).

Remark

If you want to directly use texts instead of cell references in the formula, please enclose text strings with double quotation marks.

Combine cells with other separators

If you want to use other separators to replace commas, change “,” in the formulas to other separators. For instance, combine with plus symbol.

Combine Two Columns In Excel

=SUBSTITUTE(TRIM(B6&' '&C6&' '&D6&' '&E6),' ','+')
Or
=CONCATENATE(B3,'+',C3,'+',D3,'+',E3)
Or
=B3&'+'&C3&'+'&D3&'+'&E3

Sample File

Combine Two Columns In Excel

Relative Formulas

Combine two columns in excel to one
  • Check if a cell contains a specific text
    To check if a cell contains some texts in range A but does not contain the texts in range B, you can use an array formula which combines the COUNT, SEARCH and AND function in Excel
  • Check if a cell contains one of several values but exclude other values
    This tutorial will provide a formula to quickly handle the task that check if a cell contains one of things but excluding other values in Excel and explain the arguments of the formula.
  • Check if cell contains one of things
    Supposing in Excel, there is a list of values in column E, you want to check if the cells in column B contain all of the values in column E, and return TRUE or FALSE.
  • Check if cell contains number
    Sometimes, you may want to check if a cell contains numeric characters. This tutorial provides a formula which will return TRUE if the cell contains number, FALSE if cell does not contain number.

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

How To Combine Two Columns In Excel

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

Combine Two Columns In Excel Concatenate

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

June 14, 2018 - by Bill Jelen

David from Florida asks today's question:

I have two workbooks. Both have the same data in column A, but the remaining columns are different. How can I merge those two workbooks?

I asked David if it is possible that one workbook has more records than the other. And the answer is Yes. I asked David if the key field only appears once in each file. The answer is also yes. Today, I will solve this with Power Query. The Power Query tools are found in Windows versions of Excel 2016+ in the Get & Transform section of the Data tab. If you have Windows versions of Excel 2010 or Excel 2013, you can download the Power Query add-in for those versions.

Combine Two Columns In Excel To One

Here is David's workbook 1. It has Product and then three columns of data.

Here is David's workbook 2. It has Product Code and then other columns. In this example, there are extra products in workbook2, but the solutions will work if either workbook has extra columns.

Here are the steps:

  1. Select Data, Get Data, From File, From Workbook:

  2. Browse to the first workbook and click OK
  3. In the Navigator dialog, choose the worksheet on the left. (Even if there is only one worksheet, you have to select it.) You will see the data on the right.
  4. In the Navigator dialog, open the Load dropdown and choose Load To...
  5. Choose Only Create a Connection and press OK.
  6. Repeat steps 1-5 for the second workbook.

    If you've done both workbooks, you should see two connections on the Queries & Connections Panel on the right of your Excel screen.

    Continue with the steps to merge the workbooks:

  7. Data, Get Data, Combine Queries, Merge.

  8. From the top drop down in the Merge dialog, choose the first query.
  9. From the second drop down in the Merge dialog, choose the second query.
  10. Click on the Product heading in the top preview (this is the key field. Note you can multi-select two or more key fields by Ctrl + Clicking)
  11. Click on the Product Code heading in the second preview.
  12. Open the Join Type and choose Full Outer (All Rows From Both)

  13. Click OK. The data preview does not show the extra rows and only shows 'Table' repeatedly in the last column.

  14. Notice there is an 'Expand' icon in the heading for DavidTwo. Click that icon.
  15. Optional, but I always unselect 'Use Original Column Name As Prefix'. Click OK.

    The results are shown in this preview:

  16. In Power Query, use Home, Close & Load.

Combine Two Columns In Excel Formula

Here is the beautiful feature: if the underlying data in either workbook changes, you can click the Refresh icon to pull new data in to the results workbook.

Note

The icon for Refresh is usually hidden. Drag the left edge of the Queries & Connections pane to the left to reveal the icon.

Watch Video

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2216: Combine Two Workbooks Based on a Common Column.

Hey, welcome back to MrExcel netcast, I'm Bill Jelen. Today's question's from David, who was in my seminar in Melbourne, Florida, for the Space Coast Chapter of the IIA.

David has two different workbooks where Column A is in common between both of them. So, here's Workbook 1, here's Workbook 2-- both have product code. This one has items that the first one doesn't have, or vice versa, and David wants to combine all the columns. So, we have three columns here and four columns here. I put both of these in the same workbook, in case you're downloading the workbook to work along. Take each one of these, move it out to its own workbook and save it.

Alright, to combine these files, we're going to use Power Query. Power Query's built into Excel 2016. If you're in the Windows version of 10 or 13, you can go out to Microsoft and download Power Query. You can start from a new blank workbook with a blank worksheet. You're going to save this file-- Save as, you know, maybe Workbook, to show the results of combined files .xlsx. Alright? And what we're going to do is, we're going to do two queries. We're going to go to Data, Get Data, From File, From Workbook, and then we'll choose the first file. In a preview, select the sheet that has your data, and we don't have to do anything to this data. So just open the load box and choose Load To, Only Create Connection, click OK. Perfect. Now, we're going to repeat that for the second item-- Data, From File, From a Workbook, choose DavidTwo, choose the sheet name, and then open the load, Load To, Only Create a Connection. You'll see over here in this panel, we have both connections present. Alright.

Now the actual work-- Data, Get Data, Combine Queries, Merge, and then in the Merge dialog, choose DavidOne, DavidTwo, and this next step is completely unintuitive. You have to do this. Choose the column or columns in common-- so Product and Product. Alright. And then, be very careful here with the join type. I want all rows from both because one might have an extra row and I need to see that, and then we click OK. Alright. And here's the initial result. It doesn't look like it worked; it doesn't look like it added the extra items that were in file 2. And we have this column 5-- it's null now. I'm going to right click column 5 and say, Remove that column. So open this expand icon and uncheck this box for Use original column name as prefix, and BAM! it works. So the extra items that were in File 2, that aren't in File 1, do appear.

Alright. Now in today's file, it looks like this Product Code column is better than this Product column, because it has extra rows. But there might be a day in the future where Workbook 1 has things that Workbook 2 doesn't have. So I'm going to leave both of them there, and I'm not going to get rid of any nulls because, like, even though this row at the bottom appears to be completely null, there might be in the future a situation where we have a few nulls in here because something's missing. Alright? So, finally, Close & Load, and we have our sixteen rows.

Now, in the future, let's say that something changes. Alright, so we'll go back to one of those two files and I'll change the class for Apple to 99, and let's even insert something new and save this workbook. Alright. And then, if we want our merge file to update, come over here-- now, watch out, when you do this the first time, you can't see the Refresh icon-- you have to grab this bar and drag it over. And we will do Refresh, and 17 rows loaded, the watermelon appears, the Apple changes to 99-- it's a beautiful thing. Now, hey, do you wanna learn about Power Query? Buy this book by Ken Puls and Miguel Escobar, M is for (DATA) MONKEY. I'll get you up to speed.

Excel Combine Two Columns Names

Wrap-up today: David from Florida has two workbooks that he wants to combine; they both have the same fields in Column A, but the other columns are all different; one workbook might have extra items that are not in the other and David wants those; there's no duplicates in either file; we're going to use power query to solve this, so start in a new blank workbook on a blank worksheet; you're going to do three queries, first one-- Data, From File, Workbook, and then Load to only Created Connection; the same thing for the second workbook, and then Data, Get Data, Merge, select the two connections, select the column that's common in both--in my case, Product-- and then from the Join Type, you want to full join all from the File 1, all from File 2. And then the beautiful thing is if the underlying data changes, you can just refresh the query.

To download the workbook from today's video, visit the URL in the YouTube description.

Well, hey, I want like David for showing up for my seminar, I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.

Combine Two Columns In Excel 2016

Download Excel File

To download the excel file: combine-based-on-common-column.xlsx

Power Query is an amazing tool in Excel.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

'Always press F4 when you read range or matrix in a function'

Coments are closed

Most Viewed Posts

  • Amazon Prime Tv Shows 2020
  • Using Tor On Iphone
  • Manycam Compatible Cameras
  • Retroarch Ps3 Games

Scroll to top