Ask Mr. Excel - Concatenate cells

Back

About MrExcel

Consulting Services

Learn Excel Resources

Challenge of the Month

Past Tip of the Week

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 them concatenated in order to do a mail merge.

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.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Excel is a registered trademark of the Microsoft® Corporation.