How to combine columns excel? If you want to combine your data, this is how to do it in Excel!
Combine data with the Ampersand symbol (&)
- Select the cell where you want to put the combined data.
- Type = and select the first cell you want to combine.
- Type & and use quotation marks with a space enclosed.
- Select the next cell you want to combine and press enter. An example formula might be =A2&” “&B2.
Combine data using the CONCAT function
- Select the cell where you want to put the combined data.
- Type =CONCAT(.
- Select the cell you want to combine first. Use commas to separate the cells you are combining and use quotation marks to add spaces, commas, or other text.
- Close the formula with a parenthesis and press Enter. An example formula might be =CONCAT(A2, ” Family”).
Can you merge columns in Excel?
First, highlight two or more columns or rows or group of cells that are adjacent to each other. Then click the Home button and then click the “Merge and Center” button in the toolbar.
Select “Merge Cells” from the drop-down options.
How do I combine 3 columns in Excel?
How do you link contents in Excel?
- Insert a sheet and select a cell.
- Press [Ctrl]+k to display the Insert Hyperlink dialogue.
- In the left pane, click Place In This Document.
- Find the sheet you want to link to under Cell Reference.
- Highlight the sheet in Excel
- Highlight the cell reference and press Delete. …
- Click OK.
How do I automatically link data from one sheet to another in Excel?
From source worksheet, select the cell that contains data or that you want to link to another worksheet, and copy it by pressing the Copy button from the Home tab or press CTRL+C. Go to the destination worksheet and click the cell where you want to link the cell from the source worksheet.
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.
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:
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)
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).
- 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“.
- 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.
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.
- 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.
- 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.
- 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.
- Remove the “First Name” & “Last Name” columns, which are not needed any longer. Click the column Bheader, 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:
Fine, we have merged the names from 2 columns into one!
Though, it required quite a lot of effort and time 🙁
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
- 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.
- Copy data to clipboard (press Ctrl + C or Ctrl + Ins, whichever you prefer).
- Open Notepad: Start-> All Programs -> Accessories -> Notepad.
- Insert data from the clipboard to the Notepad (Press Ctrl + V or Shift + Ins)
- Copy tab character to clipboard. Press Tab right in Notepad, press Ctrl + Shift + Home, then press Ctrl + X.
- 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.
- Press Ctr + A to select all the text in Notepad, then press Ctrl + C to copy it to Clipboard.
- Switch back to your Excel worksheet (press Alt + Tab), select just B1 cell and paste text from the Clipboard to your table.
- 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
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
- Download and install the Ultimate Suite.
- Select all cells from 2 or more columns that you want to merge, go to the Ablebits.com Datatab > Merge group, and click Merge Cells > Merge Columns into One.
- 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
- Make sure the Clear the contents of selected cells option is ticked and click Merge.
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.