See all Microsoft Excel tips
Strategy: Use the ampersand (&) as a concatenation operator in a formula in column C. Change the formulas in column C to values before deleting columns A and B. Follow these steps.
1) In cell C2, enter the formula =A2&B2, as shown in Fig. 258.
2) You need to insert a space between the first name and last name. If you join cell A2, a space in quotes, and cell B2, the answer will look acceptable. The formula is =A2&“ ”&B2. Copy this formula down to all of the cells in your range, as shown in Fig. 259.
Additional Information: To convert BRITNEY SPEARS to Britney Spears, use the PROPER function. =PROPER(A2&“ ”&B2) will convert the names to Proper Case, as shown in Fig. 260. This will work for all of your names except names with an interior capital such as “Paul Mc- Cartney” or “Dave VanHorn”. After using the PROPER function, you will have to manually fix any names with an interior capital letter.
Gotcha: If you delete columns A and B while column C still contains formulas, then all of the formulas will change to #REF! errors, as shown in Fig. 261. This tells you that you have a formula that points to cells(s) that are no longer there. Immediately hit Ctrl+Z to undo the delete.
To work around this situation, first convert all of the formulas in column C to values. Follow these steps:
1) Select the data in column C.
2) Select Ctrl+C to copy the data to the clipboard.
3) Without changing the selection, select Edit – Paste Special….
4) In the Paste Special dialog box, choose Values and then OK, as shown in Fig. 262.
5) This converts column C from live formulas to static values. You can now delete columns A and B.
Summary: The ampersand (&) is the concatenation character used to join text cells with other text cells or with literal values in a formula.
Commands Discussed: Edit – Paste Special
Functions Discussed: =PROPER()
See all Microsoft Excel tips
