Pin Me

Microsoft Excel: How To Combine Two Text Columns

written by: Mr Excel•edited by: Michele McDonough•updated: 4/9/2010

If you have data in two columns that you'd like to combine into one single column in Excel, you can do so by using the ampersand (&) to concatenate the two cells. Read on to find out how.

  • slide 1 of 3

    Combining Columns in Excel

    Fig. 257 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.

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

    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.

    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.

    Fig. 260 

    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.

    Fig. 261 

    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.

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

  • slide 3 of 3

    More Excel Tips

    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: