Microsoft Excel: How To Combine Two Text Columns

Problem: As shown in Fig. 257 (click any image for a larger view), you have data with First Name in column A and Last Name in column B. You want to combine these into one column.

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.

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()

Interested in learning other Excel tips and tricks? Be sure to take a look at the hundreds of other Microsoft Excel user guides and tutorials available here at Bright Hub, including:

• 91 Tips for Calculating with Microsoft Excel

• Excel Formatting Tips from Mr. Excel