Building Excel 2010 Formulas with the Insert Function Dialog Box
Creating Complex Formulas in Excel 2010
 
Using AutoSum for Quick Calculations in Excel 2010

How to Join Text Strings in Excel 2010 with the & Operator

In Excel 2010, you can use the ampersand (&) operator to concatenate (or join) separate text strings together. For example, in a client list worksheet that includes the last names in column A and the first names in column B, you could use this operator to join together the first and last names into a single entry (with the first and last names separated by a space) in column C. Refer to the example in the following figure.

This worksheet lists the last and first names of clients in separate columns.
This worksheet lists the last and first names of clients in separate columns.

To join the first name entry in cell B3 with the last name entry in cell A3, you enter the following formula in cell C3:

=B3&" "&A3

Notice the use of the double quotes in this formula. They enclose a blank space that is placed between the first and last names. If you don't include this space in the formula and just join the first and last names together with this formula:

=B3&A3

Excel would return ChristopherAiken to cell C3, all as one word.

After entering the concatenation formula that joins the first and last names in cell C3 separated by a single space, you can then drag the Fill handle (the bottom-right corner of the cell) in cell C3 down to cell C17 to copy the formula and join all the other client names in a single cell in column C.

Worksheet after concatenating the first and last names in column C.
Worksheet after concatenating the first and last names in column C.

After the original concatenation formula is copied down the rows of column C, you can copy the selected cell range C3:C17 to the Clipboard by clicking the Copy button in the Clipboard group of the Home tab, and then immediately click the Paste Values option on the Paste command button's drop-down menu on the Home tab. This pastes calculated text values over the concatenation formulas, thereby replacing the original formulas. The result is a list of first and last names together in the same cells in the range C3:C17.

blog comments powered by Disqus
Excel Order of Operations to Keep in Mind
Excel Cell References Worth Remembering
Referring to Values or Formulas in Other Cells in Excel 2010 Formulas
Using Parentheses to Change the Order of Operations in Excel 2010 Formulas
Using Absolute and Relative References in Excel 2010 Formulas
Advertisement

Inside Dummies.com