- Excel Book Excerpt
Excel Join Two Text Columns
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
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.
Figure 219 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:
1. In cell C2, enter the formula =A2&B2.
Figure 220 Use & to join text.
2. 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.
3. Copy the formula down to all the cells in the range.
Figure 221 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.
Figure 222 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.
Figure 223 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:
1. Select the data in column C.
2. Press Ctrl+C to copy the data to the Clipboard.
3. Without changing the selection, select Home, Paste dropdown, Paste Values. In Excel 2007, instead of an icon, look for “Paste Values”.
4. You can now delete columns A and B.
Figure 224 Convert formulas to values.
For more resources for Microsoft Excel: