Problem: I have data with first names in column A and last names in column B. I want to merge these two columns into one column.
- And here's how you can accomplish the task: On your Excel ribbon, go to the Ablebits tab Merge group, and click the Combine Sheets button: Select all the worksheets you want to merge into one. If you'd like to combine just one table, not all data, hover over. Choose the columns you want to.
- Merge Two Columns using Excel Formulas 1. Into your table insert a new column in the column header place the mouse pointer right-click the mouse select Insert from the context menu. Name the newly added columns for eg.
Jul 29, 2020 Combine Columns with the CONCAT Function Click the cell where you want the combined data to go.
Combine Multiple Columns Into One
How To Combine 2 Columns Into 1 In Excel 2010
- You want to join A2 and B2 into a single cell.
Strategy: You can use the ampersand (&) as a concatenation operator in a formula in column C. You change the formulas in column C to values before deleting columns A and B. These are the steps:
- In cell C2, enter the formula =A2&B2.
- Use & to join text.
- To insert a space between the first name and the last name, join cell A2, a space in quotes, and cell B2, using the formula =A2&' '&B2.
- Copy the formula down to all the cells in the range.
- Join A2, a space, and B2.
Additional Details: To convert NORAH JONES to Norah Jones, you use the PROPER function. =PROPER(A2&' '&B2) will convert the names to proper case. This will work for all your names except names with interior capitals, such as Paul McCartney or Dave VanHorn. After using the PROPER function, you will have to manually fix any names that have interior capital letters. (Some people suggest entering the last name as Mc Cartney, with a space to prevent this problem.) Note: If you like PROPER, consider UPPER and LOWER to convert text to upper or lower case.
- There are also UPPER and LOWER functions.
Gotcha: If you delete columns A and B while column C still contains formulas, all the formulas will change to #REF! errors. This tells you that you have a formula that points to cells(s) that are no longer there. You can immediately press Ctrl+Z (or Alt+Backspace) to undo the deletion.
- Delete A:B, the live formulas in C will change to #REF! errors.
To work around this situation, you first convert all the formulas in column C to values. Follow these steps:
- Select the data in column C.
- Press Ctrl+C to copy the data to the Clipboard.
- Without changing the selection, select Home, Paste dropdown, Paste Values.
- You can now delete columns A and B.
- Convert formulas to values.