EXCEL_VBA_CleanPhoneNumber_Function

EXCEL_VBA_CleanPhoneNumber.vba

Function cleanPhoneNumber(thisNumber As String) As String
    ' this function aspires to clean any phone number format
    ' to standard format (+9999) 999-999-9999 or 999-999-9999
    ' works with almost all phone number formats stored in text

Dim retNumber As String

For i = 1 To Len(thisNumber)
    If Asc(Mid(thisNumber, i, 1)) >= Asc("0") And Asc(Mid(thisNumber, i, 1)) <= Asc("9") Then
        retNumber = retNumber + Mid(thisNumber, i, 1)
    End If
Next
If Len(retNumber) > 6 Then

    If Len(retNumber) > 10 Then
        ' format for country code as well
        cleanPhoneNumber = Format(retNumber, "+#(000)000-0000")
    Else
        cleanPhoneNumber = Format(retNumber, "+1(000)000-0000")
    End If

Else
    If Left(thisNumber, 1) = "3" Then
        cleanPhoneNumber = Format(retNumber, "+1(303)720-0000")
    
    ElseIf Left(thisNumber, 1) = "4" Then
        cleanPhoneNumber = Format(retNumber, "+1(720)510-0000")
    
    End If

End If

End Function