Microsoft Excel - How to strip special characters from a phone number??

Asked By Craig MacLennan on 08-Jun-09 04:18 PM

Hi Guys,

I have an exporeted customer list from our accounting program and I am trying to strip all of the special characters from the phone number fields in order to use it in XML can anyone help me with a formula or instructions on how to go about this?

Thanks in Advance

One possible solution - Rolf Jaeger replied to Craig MacLennan on 08-Jun-09 05:47 PM

I am not entirely sure what you mean by 'special characters' in the context of phone numbers. Assuming that you would like (xxx) yyy-zzzz to get converted to xxxyyyzzzz the functio I am listing below should do the trick. Simply copy it into a VBA module and then refer to it in your worksheet by entering  = STRIPSPECIALCHARACTERS(+A1) in an empty cell where cell A1 contains the phone number you would like to strip. As you can see I left room for more special characters.

Best wishes,

Function StripSpecialCharacters(s As String)
    Dim spChars(10) As String
    spChars(0) = "("
    spChars(1) = ")"
    spChars(2) = "."
    spChars(3) = "-"
    spChars(4) = ""
    spChars(5) = ""
    spChars(6) = ""
    spChars(7) = ""
    spChars(8) = ""
    spChars(9) = ""
    Dim c As Variant
    For Each c In spChars
        If c <> "" Then
            s = Replace(s, c, "")
        End If
    StripSpecialCharacters = s
End Function