MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Join Excel Cell Values


June 04, 2002 - by Bill Jelen

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).


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.