Converting Phone Number Formats 3

[kopy]

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.
Thanks,
Kopy

 

[Andrzejek]

Dim strPN As String

strPN = "(617) 868-8332"

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

MsgBox strPN

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[sxschech]

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

 

[MajP]

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

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
    Else
      PhoneFormat = Phone
    End If
  End If
End Function

 

[MajP]

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

 

[kopy]

MajP,

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

Works like a charm!

Thank you so very much,

Kopy