Join Text in Excel

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.

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

For more tips like this page, check out MrExcel's book:

Learn Excel 97-2007 from MrExcel
buy now more info

Articles