Excelbud

Owner and site operator for Excelbuddy.com

Excel’s concatenate function is used to combine character strings from multiple cells. This function can be extremely useful if you need to perform a manual task on a large list of data.

The example is this tutorial will demonstrate how to combine an area code in column “A” with a phone number in column “B.” We will also be including the dash mark

1. To begin, start with a list like shown below. As you can see, in column “A” we have the area code and in column “B” we have the phone number.
2. Instead of manually copying or retyping each number, we can use the Concatenate formula. In Cell C2, type the following forumula. =CONCATENATE(A2,”-”,B2) You can change the dash mark “-” to any other character, however, it must be in quotations marks.

   

3. Once you hit enter, the phone number will be displayed properly. Simply drag the formula down using the Auto Fill function.
   

Error Message Fix

If you decide you want to delete columns “A” and “B” from the sheet, you may notice you receive an #REF! error.  Since destination cells, in this case “C2″ only contain formulas, we need columns “A” and “B” to grab our value.  However, here is  a simple trick to bypass this annoying error.

1. Highlight and select your list of numbers in column “C.”
2. Once selected, right click and hit “Copy.”
3. Once copied, hit “Paste Special.”
4. Select the “Values” radio box and hit “OK”

Now you can delete columns “A” and “B” without losing your data.

If you have any questions, please comment below or use the contact page.