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.
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.
Give a name for Macro, enter a shortcut and click OK.
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.
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.
Finally, go to the Developer tab, and click on Stop Recording.
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.
That’s all!
