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