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