Function NormalizeString(Optional ByVal value As Variant) As String
If value = vbNullString Then
NormalizeString = ""
Else
NormalizeString = StrConv(value, vbUpperCase)
End If
End Function
Function getPhoneDigit(Optional ByVal value As Variant) As Variant
getPhoneDigit = NormalizeString(value)
Select Case value
Case "A", "B", "C"
getPhoneDigit = 2
Case "D", "E", "F"
getPhoneDigit = 3
Case "G", "H", "I"
getPhoneDigit = 4
Case "J", "K", "L"
getPhoneDigit = 5
Case "M", "N", "O"
getPhoneDigit = 6
Case "P", "Q", "R", "S"
getPhoneDigit = 7
Case "T", "U", "V"
getPhoneDigit = 8
Case "W", "X", "Y", "Z"
getPhoneDigit = 9
End Select
End Function
Function PhoneNumber(Optional ByVal value As Variant = "") As String
Dim charArray() As String
Dim index As Long
value = StrConv(value, vbUnicode)
value = NormalizeString(value)
charArray = Split(value, vbNullChar)
For index = LBound(charArray) To UBound(charArray)
charArray(index) = getPhoneDigit(charArray(index))
Next index
PhoneNumber = Join(charArray, "")
End Function