MrExcel.com - Your One Stop for Excel Tips & Solutions

Join Excel Cell Values

June 04, 2002 - by

Valerie writes:

I am an Excel novice. I have imported a file into Excel. The zip code is broken into 2 cells. The 5 digit part of the zip code is in column E, the 4 digit part of the zip code is in column F. I need to join them into a single cell.

For our non-US readers, Valerie needs the zip code in the form of 12345-6789. Cell E2 has a value of 12345 and cell F2 has a value of 6789.

In Excel, the ampersand is the concatenation character. Enter the following formula in G2: =E2&"-"&F2

After copying the formula in G2 down to all of the cells in column G, Valerie may be tempted to delete columns E & F. If you would delete these columns while there are "live" formulas which point to the deleted cells, the live formulas will change to #REF!.

To change live formulas to values, do the following:

  • Highlight all of the cells with formulas
  • From the menu, select Edit > Copy
  • From the menu, select Edit > Paste Special
  • In the Paste Special dialog, click values in the top section of the dialog
  • Click OK

The formulas in column G will change to values. You can now delete cells E & F.

Tip

What if you need to join first name and last name? To put a space between the names, use =E2&" "&F2. If you want the names in proper case, use =PROPER(E2&" "&F2).