Is there a way of inserting hyphens (-) into all of the existing phone numbers contained in my Outlook 2007 contacts list? So the format will be 123-456-7890 instead of 1234567890

Is there a way of inserting hyphens (-) into all of the existing phone numbers contained in my Outlook 2007 contacts list? So the format will be 123-456-7890 instead of 1234567890.

What is the easiest way of dong this? As I have mentioned, most all of the phone numbers currently don't contain hyphens.

I know that one way of doing this might be to export all of the data into Microsoft Excel and then format the Microsoft Excel columns and then import all of this data back into my Outlook contacts. However, I am looking for an easier way of doing this.

How can this be done?
IT GuyNetwork EngineerAsked:
Who is Participating?
 
LingerLongerCommented:
You could use something like Data Fields Manager - http://www.datafieldsmanager.com/. Unfortunately, not a freebie.
0
 
jasfoutCommented:
With the help of:
http://www.freevbcode.com/ShowCode.asp?ID=1968

I was able customize to do what you are asking using vba

From Outlook <Alt>+<F11>
Insert a new module
Paste the contents of the code snippet below into that module
Run the sub FixPnumbers


Private Sub FixPNumbers()
    Dim items As items, item As ContactItem, folder As folder
    Dim contactItems As Outlook.items
    Dim itemContact As Outlook.ContactItem

    Set folder = Session.GetDefaultFolder(olFolderContacts)
    Set items = folder.items

    Set contactItems = items.Restrict("[MessageClass]='IPM.Contact'")
  
    For Each itemContact In contactItems
        
        itemContact.MobileTelephoneNumber = FormatPhoneNumber(itemContact.MobileTelephoneNumber)
        ' add aditional number fields as needed
        itemContact.BusinessTelephoneNumber = FormatPhoneNumber(itemContact.BusinessTelephoneNumber)
        
        itemContact.Save
    Next
End Sub





'Pulled from http://www.freevbcode.com/ShowCode.asp?ID=1968
'Takes any entered phone number and returns it in ###-#### format
'or (###) ###-####

Public Function FormatPhoneNumber(ByVal sNumToBeFormatted As _
   String) As String

Dim iNumberLength As Integer 'Used for the Phone Number length
   
'Trim any leading and trailing spaces

sNumToBeFormatted = Trim$(sNumToBeFormatted)
   
'Length of the phone number.

iNumberLength = Len(sNumToBeFormatted)
   
Select Case iNumberLength

  Case 7  'Format : #######

    FormatPhoneNumber = Left$(sNumToBeFormatted, 3) & _
        "-" & Right$(sNumToBeFormatted, 4)
    Exit Function

  Case 8  'Format : ###-#### or ### ####

    If Mid$(sNumToBeFormatted, 4, 1) = "-" Then
       FormatPhoneNumber = sNumToBeFormatted
       Exit Function
    Else
       FormatPhoneNumber = Left$(sNumToBeFormatted, 3) & "-" & _
          Right$(sNumToBeFormatted, 4)
       Exit Function
    End If

  Case 10 'Format : ##########

 FormatPhoneNumber = "(" & Left$(sNumToBeFormatted, 3) & ") " _
   & Mid$(sNumToBeFormatted, 4, 3) & "-" & _
     Right$(sNumToBeFormatted, 4)
 
   Exit Function

  Case 11 'Format ######-####

 FormatPhoneNumber = "(" & Left$(sNumToBeFormatted, 3) & ") " & _
       Right$(sNumToBeFormatted, 8)
    Exit Function

  Case 12 'Format : ### ###-####

 FormatPhoneNumber = "(" & Left$(sNumToBeFormatted, 3) & ") " & _
      Mid$(sNumToBeFormatted, 5, 3) & "-" & _
      Right$(sNumToBeFormatted, 4)
    Exit Function

  Case 13 'Format : (###)###-####
     FormatPhoneNumber = Left(sNumToBeFormatted, 5) & " " & _
        Right(sNumToBeFormatted, 8)
     Exit Function


  Case Else
        'Return Value Passed
     FormatPhoneNumber = sNumToBeFormatted
           
End Select

End Function

Open in new window

0
 
jasfoutCommented:
Good question BTW
 I had a few numbers like that in my contacts, never really cared much, but this cleaned it up.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
IT GuyNetwork EngineerAuthor Commented:
jasfout,

When I tried to compile the code using the instructions that you provided, I received the following error: "Compile error: Sub or Function not defined." (Refer to the screenshots).

What do I need to do to resolve this error so that I can execute the code that you have provided?
Complile-error.JPG
0
 
IT GuyNetwork EngineerAuthor Commented:
Compile error-Full screen screenshot.
Outlook-VB-error.JPG
0
 
jasfoutCommented:
it looks like you are missing second part which is the:
FormatPhoneNumber
function

Copy & paste everything in the snippet into that module
0
 
jasfoutCommented:
The second part begins with:
'Pulled from http://www.freevbcode.com/ShowCode.asp?ID=1968

0
 
IT GuyNetwork EngineerAuthor Commented:
jasfout,

Now I'm getting an "Invalid Procdeure Name" error message.

What can be done to fix this so that the code executes properly?
0
 
jasfoutCommented:
can you please paste the code you have in the module?
0
 
jasfoutCommented:
I meant, can you please post the code from your entire module for us to see here.
0
 
migadminCommented:
If you configure dialing options on your system, specifying your country and area code, Outlook will automatically convert numbers into (XXX) XXX-XXXX format.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.