Combine two or more columns by using a function

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

Suppose you have two or more columns of data that you want to combine in a single column, such as the name and phone number of a person. To combine two or more columns, use the CONCATENATE function in a formula in a nearby cell (typically to the right of the last column of data that you want to combine), and then drag that formula down through the rows that contain the data. When you create your formula, you can add a space or comma to cleanly separate names and addresses in the new column by enclosing them in quotation marks. You can also use the CHAR function and an ASCII code to insert a special character, such as a line break, when you're combining the data so that names are on a separate line from street addresses and city, state, and postal codes.

Example

The example may be easier to understand if you copy it to a blank worksheet.

  1. Select the text in the example, starting with “First name” and ending with the phone number in the last row.
  2. Press CTRL+C to copy the text.
  3. In the worksheet, select cell A1, and press CTRL+V.

    To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note   To see the result in the worksheet without breaking the lines of the name or address, expand the width of the columns.

First name Last name    Phone Function

Steve

Riley

555-1875 ext. 2007 =CONCATENATE(A2," ",B2," ",C2)

Terry

Adams

555-2306

Jim

Hance

555-1896

Wendy

Wheeler

555-2307 ext. 705

The formula in the FUNCTION column of the preceding example table uses the CONCATENATE function to combine the contents of three cells in columns A, B, and C. In the function, you separate the strings that you want combined by commas. To add a space between the strings, include a space enclosed in quotation marks (" "). If you look closely at the CONCATENATE function in the formula, you’ll see that the contents of A2 are combined with a space, the contents of B2, another space, and the contents of C2.

After you paste the example into your worksheet, drag the formula in D2 down to cells D3:D5 to combine the contents of those cells.

Here’s a similar example that uses the CHAR function to insert a new line. These results are better suited for a mailing label.

First name Last name    Street address City State Zip code

Steve

Riley

4567 Main St. Buffalo NY 98052
Function Description
=CONCATENATE("The ", B2, " Family", CHAR(10), C2, CHAR(10), D2, ", ", E2, " ", F2)

Combines the last name with the words "The" and "Family" and then combines the address with it. CHAR(10) is used to insert a new line.

Result:

The Riley Family
4567 Main St.
Buffalo, NY 98052

The preceding example uses the CHAR function inside the CONCATENATE function to insert a character that can be difficult to type in a formula (such as a comma or a space character, because either one might make the formula hard to read), or some other non-alphanumeric character, such as a line break or symbol. In the example, CHAR(10) is used to enter line breaks in the cell.

The CHAR function requires a number value between a pair of parentheses. The function passes this number to Excel, which returns the character that corresponds to that number. The line break character is represented by the number 10.

For more information about these functions, see CONCATENATE function and CHAR function.

 
 
Applies to:
Excel 2010, Excel 2007, Excel 2003