Want to combine the contents of two or more cells into one cell?  There’s a formula for that!

Let’s say I have a spreadsheet with a column for first names and a column for last names.  I want just one column that combines first and last name.  I’ll write a formula that uses the & to do so.  In a formula, the & combines what comes before it with what comes after it.

The formula to combine A1 and B1 together, like in the example below, would be:

=A1&B1

Of course, I would like to have a space between the first and the last name.  If you want to insert something other than a cell reference, you need to put it inside quotation marks, and between &s.  So, the formula to combine the first and last name, and put a space between them would be:

=A1&” “&B1

Now, if I double click on the fill handle (circled in red below), it will fill the formula down as far as I have continuous data in the column to the left.

If you delete any of the original data, the information will disappear from the third column, because the third column only displays the results of the formula.  In the example below, I deleted the contents of the first column, and now the formula only displays the contents of the second column.

To keep the results of the formula, but not have it tied to the other two columns, you will have to copy the data, and paste the values. 

Paste Values in Excel 2007

  1. Copy the data.
  2. Select the place you would like to place it.  It can be the same area you copied.
  3. On the Home tab of the ribbon, click on the bottom half of the Paste button.
  4. Click on Paste Values.

Paste Values in Excel 2003

  1. Copy the data.
  2. Select the place you would like to place it.  It can be the same area you copied.
  3. Go to the Edit menu and select Paste Special.
  4. Select the button next to Values.
  5. Click OK.

You can see by looking in the formula bar in the example below, that the formula has been replaced with the value in the cell.