In this article, I shall show you how to change the Phone number formatting in MS Excel sheet, using VBA macros. You have to create a Macro for this. Before that, check out what is VBA and why is it important?

Steps To Change Phone Number Formatting Of Phone Numbers In An Excel Sheet Using VBA Macro

Let me create a small table for you, which consists of name and phone numbers. For that, you have to select Phone number as the special category. By default, the phone number category is always for the United States, that is, (###)###-###. So, if you enter a 10 digit number for another country, e.g. India, you have to change the formatting style, otherwise the number you entered will indicate an US phone number.

To Change Phone Number Formatting Of Phone Numbers In An Excel Sheet Using VBA Macro

The table I created is very small, so you may not be able to understand the point of creating a macro. But in real cases, the Excel sheets consist of thousands of rows and a macro will definitely solve your problem much easily. In the table, you see there are some phone numbers, whose formatting are for United States. Suppose I want to change the formatting to that of India. Let’s see how to do it.

First, you have to turn on the Developer Tab in MS Excel.

Next, click on the Developer tab and turn on the Use Relative References. Before selecting Record Macro, select a cell. Now, select Record Macro.

To Change Phone Number Formatting Of Phone Numbers In An Excel Sheet Using VBA Macro

Give a name for Macro, enter a shortcut and click OK.

To Change Phone Number Formatting Of Phone Numbers In An Excel Sheet Using VBA Macro

The Macro Recording has started. Go to Home tab in the Ribbon, click on the More Options drop down arrow just below the Number tab.

To Change Phone Number Formatting Of Phone Numbers In An Excel Sheet Using VBA Macro

As the Number tab opens, select the Custom option in the left tab. In the right, under the Type menu, the format is given. Delete it and change it to your preferred format. For India, my required format is (+91)########## where (+91) is India’s ISD code and the 10 hashes represent 10 digits of a number. Finally, click on OK.

To Change Phone Number Formatting Of Phone Numbers In An Excel Sheet Using VBA Macro

Finally, go to the Developer tab, and click on Stop Recording.

To Change Phone Number Formatting Of Phone Numbers In An Excel Sheet Using VBA Macro

Your Macro is now created. To apply to all the cells of a column, press Ctrl+Shift+Down_Arrow to select all cells of the column. Now click the shortcut of the macro, in my case, it is Ctrl+Shift+U.

To Change Phone Number Formatting Of Phone Numbers In An Excel Sheet Using VBA Macro

That’s all!

Read More: List Of All Excel VBA Tutorials

About 

Happiness is that best therapy. Use it to heal yourself and then others!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.