- slide 1 of 3
Combining Columns in Excel
One way to do this is with Excel's CONCATENATE function. However, if the the columns are text fields, as in this example, there is an easier way to combine them using the ampersand (&) operator.
- slide 2 of 3
How to Use the "&" to Join Two Columns
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. For more on the PROPER function and related tools, see Excel Text Function Variations.
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()