Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Converting Phone Number Formats

Converting Phone Number Formats

Converting Phone Number Formats


I need to convert phone numbers with format like (617) 868-8332 or 617/731-3413 to 617-868-8832 and 617-731-3413. Any thoughts on the best way to do this will be greatly appreciated.

RE: Converting Phone Number Formats


Dim strPN As String

strPN = "(617) 868-8332"

strPN = Replace(strPN, "(", "")
strPN = Replace(strPN, ")", "")
strPN = Replace(strPN, "/", " ") 'for 617/731-3413

MsgBox strPN 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Converting Phone Number Formats

strPN = Replace(strPN, ") ", "-")
strPN = Replace(strPN, "/", "-") 'for 617/731-3413

RE: Converting Phone Number Formats


"UPDATE tblPhone SET tblPhone.Phone = PhoneFormat([phone])"

CODE -->

Public Function PhoneFormat(Phone As Variant) As Variant
  Dim i As Integer
  Dim char As String
  Dim cleanFormat As String
  If Not IsNull(Phone) Then
    For i = 1 To Len(Phone)
        char = Mid(Phone, i, 1)
        'strip only the digits
        If Asc(char) > 47 And Asc(char) < 58 Then
          cleanFormat = cleanFormat & char
        End If
    Next i
    Debug.Print cleanFormat & vbCrLf
    If Len(cleanFormat) = 10 Then
      For i = 1 To 10
        char = Mid(cleanFormat, i, 1)
        PhoneFormat = PhoneFormat & char
        If i = 3 Or i = 6 Then PhoneFormat = PhoneFormat & "-"
      Next i
      PhoneFormat = Phone
    End If
  End If
End Function 

RE: Converting Phone Number Formats

BTW. The reason for this long code was to handle other possible cases that could be there.
617 868 8332

RE: Converting Phone Number Formats


Thank you. And yes I did have those variations to deal with.

Works like a charm!

Thank you so very much,


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close