Have Two Columns Join Into One Column Again Squarepace

From this short commodity you will acquire how to merge multiple Excel columns into one without losing data.

Y'all have a table in Excel and what y'all desire is to combine 2 columns, row-by-row, into one. For case, you want to merge the First Name & Last Proper name columns into 1, or bring together several columns such every bit Street, City, Nix, Land into a single "Address" column, separating the values with a comma so that y'all can impress the addresses on envelops later.

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

Y'all volition get the error bulletin "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 prison cell will keep the upper-left most data merely." (Excel 2010, 2007)

Further in this article, yous volition detect iii 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 mode, skip the first two, and head over to the 3rd straight away.

• Merge ii columns using formulas
• Combine columns data via NotePad
• The fastest way to join multiple columns

Merge two columns using Excel formulas

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

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

   B2 and C2 are the addresses of First Proper noun and Last Name, respectively. Notation that at that place 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 whatsoever other symbol as a separator, e.g. a comma.
   

   In a like manner, yous can join data from several cells into one, using any separator of your choice. For instance, you tin can combine addresses from 3 columns (Street, City, Zero) into one.
   

3. Copy the formula to all other cells of the Total Proper name cavalcade. For pace-by-footstep instructions please see how to enter the same formula into all selected cells at a time.
4. Well, we have combined the names from two columns in to one, but this is still the formula. If we delete the First name and /or the Concluding name, the respective data in the Full Name column will also be gone.
   
5. Now nosotros 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" cavalcade, and and so press Ctrl + Shift + ArrowDown).

   Re-create the contents of the cavalcade to clipboard (Ctrl + C or Ctrl + Ins, whichever yous prefer), then right click on any prison cell in the aforementioned column ("Full Proper name" ) and select "Paste Special" from the context carte du jour. Select the "Values" radio button and click OK.
   

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

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

Fine, we have merged the names from 2 columns into i!
Though, information technology required quite a lot of effort and fourth dimension :(

Combine columns information via Notepad

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

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

1. Select both columns we want to merge: click on B1, press Shift + ArrrowRight to select C1, and then press Ctrl + Shift + ArrowDown to select all the cells with information in ii columns.
   
2. Copy data to clipboard (press Ctrl + C or Ctrl + Ins, whichever you prefer).
3. Open Notepad: First-> All Programs -> Accessories -> Notepad.
4. Insert data from the clipboard to the Notepad (Press Ctrl + V or Shift + Ins)
   
5. Copy tab graphic symbol to clipboard. Printing Tab right in Notepad, printing Ctrl + Shift + Home, then press Ctrl + X.
6. Supercede 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 "Detect what" field, type your separator, eg. Infinite, comma etc. in the "Replace with" field. Press the "Replace All" button; then printing "Cancel" to close the dialog box.
   

7. Printing Ctr + A to select all the text in Notepad, and then press Ctrl + C to copy information technology to Clipboard.
8. Switch back to your Excel worksheet (printing Alt + Tab), select just B1 prison cell and paste text from the Clipboard to your tabular array.
   
9. Rename column B to "Full Name" and delete the "Last proper name" column.

There are more than steps than in the previous pick, simply believe me or endeavour 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 1 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, railroad vehicle render or line pause). 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 uncomplicated 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 Ablebits.com Informationtab > Merge group, and click Merge Cells > Merge Columns into One.
   
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 example)
   • Place the results to: left column
4. Make sure the Clear the contents of selected cells pick is ticked and click Merge.
   

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

To end upward, rename column B to Total Proper name and delete column "C", which is not needed any longer.

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

Y'all may also be interested in

smalldivere.blogspot.com

Source: https://www.ablebits.com/office-addins-blog/2013/10/13/merge-columns-excel-without-losing-data/

Share this post