Excel: Format Phone Numbers with Brackets and Hyphens

This week I bring you the solution to a problem that was brought to me by someone very frustrated in dealing with a Microsoft Excel spreadsheet full of phone numbers. She was looking to use the phone numbers to create a directory using the mail merge feature in Microsoft Word. Unfortunately, the phone numbers were all formatted incorrectly for what she needed. Every number appeared without any brackets or hyphens.

She had numbers like this: 5555558173
But wanted numbers like this: (555) 555-8173

You can have Excel add in those brackets and hyphens automatically. You will want to use a combination of CONCATENATE, LEFT, MID, RIGHT, and TRIM.

For this example, I'm assuming that your first phone number is in cell A1, and you would like it to be formatted correctly and appear in cell B1. In cell B1, you are going to want to enter the following formula:

=CONCATENATE("(",LEFT(TRIM(A1),3),") ",MID(TRIM(A1),4,3),"-",RIGHT(TRIM(A1),4))

Here's a screenshot of what it will look like (click to view a larger version):


That's it! Of course, this is for the specific style of phone numbers that we use here in North America. You might very well need to adjust your concatenate formula if you are using a different format.

Not quite sure how concatenation works? Click here to read a previous post I made, which should help clear up any confusion you might have on the topic.

Categories: concatenate, Excel, formulas, Microsoft Office 2007, Microsoft Office 2010