Combine the contents of multiple cells

You can use the ampersand (&) operator in a formula to combine text from multiple cells into one cell, such as a first name and a last name.

  1. Select the cell in which you want to combine the contents of other cells.
  2. To start the formula, type =(
  3. To combine the contents of two cells, select the first cell that contains the text that you want to combine, type &" "& (a space enclosed in quotation marks), and then select the next cell that contains the text that you want to combine.

To combine the contents of more than two cells, continue selecting cells, making sure to type &" "& after you select each cell. If you don't want to add a space between combined text, type & instead of &" "&. To insert a comma, type &", "& (a comma followed by a space, both enclosed in quotation marks).

  1. To complete the formula, type )
  2. To see the results of the formula, press ENTER.

 Tip   You can also use the CONCATENATE function to combine text from multiple cells into one cell, as shown in the following example.

Example

The following example worksheet shows samples of formulas that you can use.

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

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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.
 
1
2
3
A B
First Name Last Name
Sara Davis
Brad Sutton
Formula Description (Result)
=A2&" "&B2 Combines the names above, separated by a space (Sara Davis)
=B3&", "&A3 Combines the names above, separated by a comma (Sutton, Brad)
=CONCATENATE(A2," ",B2) Combines the names above, separated by a space (Sara Davis)

 Note   The formula inserts a space between the first and last names by using a space enclosed within quotation marks. Use quotation marks to include any literal text — text that does not change — in the result.

Top of Page Top of Page

 
 
Applies to:
Excel 2007