|
In Excel 2007, you can use the ampersand (&) operator to concatenate (or join) separate text strings together. For example, in the Client list spreadsheet shown in Figure 1, you can use this operator to join together the first and last names currently entered in two side-by-side cells into a single entry, as shown in Figure 2.
To join the first name entry in cell B3 with the last name entry in cell A3, enter the following formula in cell C3:
=B3&" "&A3
Figure 1: A client list with the first and last names separated.
Figure 2: Spreadsheet after concatenating the first and last names in column C.
Notice the use of the double quotes in this formula. They enclose a blank space that is placed between the first and last names joined to them with the two concatenation operators. 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, drag the Fill handle in cell C3 down to C17 to join all the other client names in a single cell in column C, as illustrated in Figure 2.
After the original concatenation formula is copied down the rows of column C, copy the selected cell range C3:C17 to the Clipboard by pressing Ctrl+C and then immediately click the Paste Values option on the Paste command button's drop-down menu. 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 cell in the range C3:C17, as though you had manually input each one.
| 
