When you sync Outlook contacts with some smartphones (like Androids), phone numbers can't be dialed because they contain the country code. This VBA code removes the +1 prefix from Outlook phone numbers. If you need to remove 2-digit country codes, you'll need to edit the function.
The code checks to see if the contact is a contact or a distribution list. If it's a distribution list, it's skipped and the code moves on to the next item.
Note that when you sync, Outlook may reformat the number. Use a 4-digit or 5-digit area code (ie, 1234 or 99999) in Control Panel's Phone and Modem Options to prevent numbers from being formatted.
Remove the country code from phone numbers
To use, select a contacts folder then open the VBA editor (use Alt+F11). Right click on the Project name and choose Insert > Module. Paste the code into the module. Press F8 or the Run button to run the macro.
This basis for this code was originally posted in the old Microsoft Newsgroups by Michal Bednarz of CodeTwo software and tweaked by Patrick. I tweaked it to use one function and check for (and skip over) distribution lists.
Sub FixPhoneFormat()
Dim oFolder As MAPIFolder
Set oFolder = Application.ActiveExplorer.CurrentFolder
' Confirm it's a contacts folder by looking at the default form name
' to see if it begins with "ipm.contact".
' This allows it to work on folders with custom forms
If Left(UCase(oFolder.DefaultMessageClass), 11) <> "IPM.CONTACT" Then
MsgBox "You need to select a Contacts folder", vbExclamation
Exit Sub
End If
Dim nCounter As Integer
nCounter = 0
Dim oItem
For Each oItem In oFolder.Items
Dim oContact As ContactItem
' If the current item is a not a Contact Group process the phone numbers
' If it's a DL, the code moves on to the next item
If TypeName(oItem) <> "DistListItem" Then
Set oContact = oItem
With oContact
.AssistantTelephoneNumber = FixFormat(.AssistantTelephoneNumber)
.Business2TelephoneNumber = FixFormat(.Business2TelephoneNumber)
.BusinessFaxNumber = FixFormat(.BusinessFaxNumber)
.BusinessTelephoneNumber = FixFormat(.BusinessTelephoneNumber)
.CallbackTelephoneNumber = FixFormat(.CallbackTelephoneNumber)
.CarTelephoneNumber = FixFormat(.CarTelephoneNumber)
.CompanyMainTelephoneNumber = FixFormat(.CompanyMainTelephoneNumber)
.Home2TelephoneNumber = FixFormat(.Home2TelephoneNumber)
.HomeFaxNumber = FixFormat(.HomeFaxNumber)
.HomeTelephoneNumber = FixFormat(.HomeTelephoneNumber)
.ISDNNumber = FixFormat(.ISDNNumber)
.MobileTelephoneNumber = FixFormat(.MobileTelephoneNumber)
.OtherFaxNumber = FixFormat(.OtherFaxNumber)
.OtherTelephoneNumber = FixFormat(.OtherTelephoneNumber)
.PagerNumber = FixFormat(.PagerNumber)
.PrimaryTelephoneNumber = FixFormat(.PrimaryTelephoneNumber)
.RadioTelephoneNumber = FixFormat(.RadioTelephoneNumber)
.TelexNumber = FixFormat(.TelexNumber)
.TTYTDDTelephoneNumber = FixFormat(.TTYTDDTelephoneNumber)
.Save
nCounter = nCounter + 1
End With
End If
Next
MsgBox nCounter & " contacts processed.", vbInformation
End Sub
Private Function FixFormat(strPhone As String) As String
strPhone = Trim(strPhone)
FixFormat = strPhone
If strPhone = "" Then Exit Function
Dim prefix As String
prefix = Left(strPhone, 1)
' Configured for US
' Enter the correct prefix here
Do While (prefix = "+" Or prefix = "1")
' if the prefix is 2 digits, change to 4;
' if 3 digits, change to 5
strPhone = Mid(strPhone, 3)
prefix = Left(strPhone, 1)
Loop
' After we clean up the country code, we remove non-numeric characters
' Can be tweaked to change formatting, ie: change 202.555.1212 to 202-555-1212
strPhone = Replace(strPhone, "(", "")
strPhone = Replace(strPhone, ")", "")
strPhone = Replace(strPhone, ".", "")
strPhone = Replace(strPhone, " ", "")
strPhone = Replace(strPhone, "-", "")
FixFormat = strPhone
End Function
Add the Country code
My function above can be tweaked to change the country code by changing the last line of the function to this format (assuming +1 was the old country code and +44 is the new country code).
FixFormat = "+44" + strPhone
Add Prefix Function
Michal's original code sample added the country code to contacts, replicating the Check Phone Number dialog.
Private Function FixFormat(strPhone As String) As String FixFormat= strPhone strPhone = Trim(strPhone) If strPhone = "" Then Exit Function If Left(strPhone, 1) = "+" Then Exit Function If Left(strPhone, 2) = "(+" Then Exit Function If Left(strPhone, 2) = "00" Then Exit Function If Left(strPhone, 3) = "(00" Then Exit Function If Left(strPhone, 1) = "1" Then Exit Function If Left(strPhone, 2) = "(1" Then Exit Function FixFormat= "1" + strPhone End Function
Change the function
You can change the function to alter phone numbers in any number of ways - if you need to add a number to dial out, change an area code, and more.
This sample checks for US number (+1) and exits the function if found, otherwise it adds 6 to the beginning of the number.
Private Function FixFormat(strPhone As String) As String strPhone = Trim(strPhone) If strPhone = "" Then Exit Function If Left(strPhone, 2) = "+1" Then Exit Function strPhone = "6" & strPhone FixFormat = strPhone End Function
jay says
Hmm, this code makes the huge simplifying assumption that all country codes are 1 digit, which they most certainly are not. In fact the only one digit country codes I see are "1" (US/Canada) and "7" (Russia). Some codes are 1 digit, some are 2, and some are three. And that's the trick, because I don't see any simple rule.
Diane Poremsky says
You just need to tweak the cod to apply it to other countries. If you have multiple codes to fix, you need to run it twice, first checking one code, then the next... .
From the code:
Bob Nitrio says
Diane,
I have tried to use the VBA script per your instructions to remove the country code from my Outlook contacts (to allow my smartphone to dial numbers correctly) and I am not seeing any changes, despite the fact that I see a message that 1700 contacts have been processed. Could the fact that I use categories be the reason for the failure? If not, what might you suggest?
Diane Poremsky says
What prefix are you removing? My guess is the formats in FixFormat aren't correct. Is anything getting changed on them?
Bob Nitrio says
I am trying to remove numeral 1 in the Country/Region field of phone numbers that adds +1 to the phone number, which leads to smartphone dialing error messages that refer to international numbers.
Nicolas says
Can you please help me with removing "area code 504" from all contacts that have that 504?
I had an issue with outlook 2016 and now most contacts have been imported with that 504 in "area code".
Diane Poremsky says
This needs changed in the macro = and assumes it is 504-123-456 format, no +1. I did not test the changes- so test any changes on a few contacts copied to a new folder.
strPhone = Trim(strPhone)
FixFormat = strPhone
If strPhone = "" Then Exit Function
Dim prefix As String
prefix = Left(strPhone, 3)
' Configured for US
' Enter the correct prefix here
Do While (prefix = "504")
' if the prefix is 2 digits, change to 4;
' if 3 digits, change to 5
strPhone = Mid(strPhone, 5)
prefix = Left(strPhone, 1)
Loop
Ethan says
Thank you very much for this info.
Tim says
Life saver. 5,200 contacts updated! Easily adapted the code to equivalent UK phone code format issues. Many many thanks.
Bertram says
Hi Diane,
Thank you very much for sharing those valuable information with us! ☺
Your script is almost exact what I am looking for. However I need to have all my phone numbers formatted like shown in the first picture.
I am working with Microsoft CRM 2011 and have to deal with lots and lots of Accounts, and contacts. All of them are synchronized with my Outlook 2010 contacts on my laptop as well as on my iPhone 4S, however, everybody types his information into the system without following a uniform "pattern".
Unfortunately I am an absolute greenhorn with this scripts and do not want to mess around with those numbers, because if I am accidentally delete one digit or switch two digits of all phone and Fax numbers, I am in big trouble. My colleagues are going to kill me. ☺
Therefore, I truly would appreciate, if you could you publish a script, which would address my needs as well? Here is what I would like to change:
All phone fax number to this format: +1 (202) 555-1414
Currently many different variations are used in the system like:
+1(202) 555-1414
+12025551414
12025551414
1.202.555.1414
1-202-555-1414
1202 555 1414
202 555 1414
202-555-1414
202.555.1414
etc. etc...
The same applies to all the text fields like, Name, Last Name, Address, Street, City, ...
I would like to have all "first letters" in Capitalised and the rest as small letters like: John, Doe, Washington...
Those fields look currently like:
JOHN DOE, WASHINGTON...
john doe, Washington...
The correct Postal code would be: V2X 3M5
Currently it looks like:
V2X-3M5
V2X3M5
v2x 3m5
v2x-3m5
v2x3m5
For the Province I would like to have the full name like: British Columbia
Currently it looks like:
BC
B.C
B. C.
B C
bc
b.c
b. c.
It would be very helpful if you could comment this script on what which function does. This would help in case I would like to adjust the script further.
I am looking forward to work with this script. This would make my daily work sooooo much easier.
Thanks Bertram
Linda Coors says
Thank you so much for this! Such a help!
KevinK says
The code works great with one exception for me - I can't get the .Save to change the record. The substitutions are made but when the function returns to the Sub, the .businessTelephoneNumber remains as it was. What could I be doing wrong?
Kaushal says
Hi. I think the solution above points to cases where one wants to change the country codes in entirety. I am based in India and have numbers of clients/friends who reside world over stored on my Google and Android handphone. I am facing the problem of the "+" sign being removed in each number when I sync my Google account with Outlook. Is there any script which just prevents removal of this "+" sign rather than replacing it?
Diane Poremsky says
It may not make a difference as it's the sync software /process that is removing it but make sure your location and dialing configuration is correct for your location in control panel, phone and modem settings. Outlook uses those settings in determining if you need a +.
The macro can be tweaked to add a + to all numbers.
John Doe says
This script rocks, thank you so much
Iemke Idsingh says
Perfect solution on https://www.slipstick.com/developer/remove-prefix-phone-number/ for all the shortcomings of Outlook in reformatting telephone numbers. My telephone numbers were all lacking the plus (+) sign in front of the number, after importing from Excel or CSV. And I need that leading plus (+) sign on my mobile phone during the many foreign travel that I do.
Outlook is trying to be too clever. You should be able to turn off the automatic formatting. Fooling around with the Phone and Modem settings is not a logic thing to do. Who would change 100's of numbers in Outlook when abroad?
Alexandre says
Thank you very much for the post, very useful. I create a Change prefix version so my operator just block the use of +55 so I need to replace with 021 due to local rules.
BR/Alexandre
Private Function ReplacePrefix(strPhone As String) As String
strPhone = Trim(strPhone)
ReplacePrefix = strPhone
If strPhone = "" Then Exit Function
Dim prefix As String
prefix = Left(strPhone, 1) 'for future use
strPhone = Replace(strPhone, "+55", "021", 1)
'MsgBox "telefone=" & strPhone
' After replace the country code, we remove non-numeric characters
' Can be tweaked to change formatting, ie: change 202.555.1212 to 202-555-1212
strPhone = Replace(strPhone, "(", "")
strPhone = Replace(strPhone, ")", "")
strPhone = Replace(strPhone, ".", "")
strPhone = Replace(strPhone, " ", "")
strPhone = Replace(strPhone, "-", "")
ReplacePrefix = strPhone
End Function