Don’t want to read all about how to do this? No problem. Just download the spreadsheet with the custom user functions already embedded and get right to work! All you’ll have to do is paste in the bad phone numbers and copy the clean results.

Click here to Download the Spreadsheet with Phone Formatting functions built in

The Problem:

When reciving a client’s data during a Salesforce migration, phone number formatting is almost always inconsistent.
 

The Solution:

Click the button above to download the spreadsheet. It contains part of an three variation of a custom, user-defined function (aka formula) that will format inconsistent phone numbers in a uniform way. There are 6 varieties of the formulas. Most likely, you’ll find one that will fit the phone number format in your instance of Salesforce.com. If it’s close, and you need it customized, please reach out.

Please leave comments or email me with questions!

For those interested in the VBA code, here it is:


 

‘******************************************************************************
‘PhoneFormatUS_Plain

‘This function formats phone numbers in US format (with area code if
‘available). Be careful when formatting international phone numbers–their
‘formatting does NOT follow simple rules.
‘******************************************************************************

Function PhoneFormatUS_Plain(varPhone As Variant, Optional fMightBeForeign As Boolean) As Variant
On Error GoTo Err_Handler
Dim strPhone As String

strPhone = HidePunct(CStr(varPhone))
If Left(strPhone, 1) = “1” Then strPhone = Mid(strPhone, 2)

Select Case Len(strPhone)
Case 7
PhoneFormatUS_Plain = Format(strPhone, “000-0000”)
Case 10
PhoneFormatUS_Plain = “1” & Format(strPhone, “0000000000”)
Case Is > 10
If fMightBeForeign Then
PhoneFormat_US = CStr(varPhone)
Else
PhoneFormatUS_Plain = “1” & Format(Left(strPhone, 10), “0000000000 “) & Mid(strPhone, 11)
End If
Case Else
PhoneFormatUS_Plain = CStr(varPhone)
End Select

Exit Function
Err_Handler:
PhoneFormatUS_Plain = varPhone
End Function

‘******************************************************************************
‘PhoneFormatUS_Dash

‘This function formats phone numbers in US format (with area code if
‘available). Be careful when formatting international phone numbers–their
‘formatting does NOT follow simple rules.
‘******************************************************************************

Function PhoneFormatUS_Dash(varPhone As Variant, Optional fMightBeForeign As Boolean) As Variant
On Error GoTo Err_Handler
Dim strPhone As String

strPhone = HidePunct(CStr(varPhone))
If Left(strPhone, 1) = “1” Then strPhone = Mid(strPhone, 2)

Select Case Len(strPhone)
Case 7
PhoneFormatUS_Dash = Format(strPhone, “000-0000”)
Case 10
PhoneFormatUS_Dash = “1-” & Format(strPhone, “000-000-0000”)
Case Is > 10
If fMightBeForeign Then
PhoneFormat_US = CStr(varPhone)
Else
PhoneFormatUS_Dash = “1-” & Format(Left(strPhone, 10), “000-000-0000 “) & Mid(strPhone, 11)
End If
Case Else
PhoneFormatUS_Dash = CStr(varPhone)
End Select

Exit Function
Err_Handler:
PhoneFormatUS_Dash = varPhone
End Function

‘******************************************************************************
‘PhoneFormatUS_Parentheses

‘This function formats phone numbers in US format (with area code if
‘available). Be careful when formatting international phone numbers–their
‘formatting does NOT follow simple rules.
‘******************************************************************************

Function PhoneFormatUS_Parentheses(varPhone As Variant, Optional fMightBeForeign As Boolean) As Variant
On Error GoTo Err_Handler
Dim strPhone As String

strPhone = HidePunct(CStr(varPhone))
If Left(strPhone, 1) = “1” Then strPhone = Mid(strPhone, 2)

Select Case Len(strPhone)
Case 7
PhoneFormatUS_Parentheses = Format(strPhone, “000-0000”)
Case 10
PhoneFormatUS_Parentheses = “1 ” & Format(strPhone, “(000) 000-0000”)
Case Is > 10
If fMightBeForeign Then
PhoneFormat_US = CStr(varPhone)
Else
PhoneFormatUS_Parentheses = “1 ” & Format(Left(strPhone, 10), “(000) 000-0000 “) & Mid(strPhone, 11)
End If
Case Else
PhoneFormatUS_Parentheses = CStr(varPhone)
End Select

Exit Function
Err_Handler:
PhoneFormatUS_Parentheses = varPhone
End Function

Function HidePunct(strInput As String) As String
HidePunct = MultiSubst(strInput, _
” “, “”, “.”, “”, “,”, “”, “&”, “”, “-“, “”, “–”, “”, “#”, “”, “”””, “”, _
“‘”, “”, “(“, “”, “)”, “”, “|”, “”, “/”, “”, “@”, “”, vbCr, “”, vbLf, “”, _
vbTab, “”, “?”, “”, “:”, “”, “+”, “”, “*”, “”, “$”, “”, “!”, “”, “%”, “”, _
“^”, “”, “\”, “”, “{“, “”, “}”, “”, “[“, “”, “]”, “”, “<", "", ">“, “”, _
“;”, “”)
End Function

Function MultiSubst(varInput As Variant, ParamArray varStrings()) As Variant
On Error GoTo Err_Handler
Dim intNum As Integer
Dim intCount As Integer
Dim strInput As String

strInput = CStr(varInput)

‘Determine the upper bound of the string array handed to MultiSubst.
intCount = UBound(varStrings())
‘Make sure that MultiSubst was handed an odd number of arguments.
If intCount Mod 2 = 0 Then
‘If it was handed an even number, return an error
MsgBox Prompt:=”Sorry, the function “”MultiSubst”” was handed the ” & _
“wrong number of arguments.”
Else
‘For each pair of parameters in the ParamArray, run the “Substitute”
‘WorksheetFunction. Note that “Substitute” handles consecutive
‘instances of text oddly; WorksheetFunction.Substitute(“aaa”,”aa”,”a”)
‘yields “aa”, not “a”.
For intNum = LBound(varStrings) + 1 To intCount Step 2
strInput = Replace(strInput, _
varStrings(intNum – 1), varStrings(intNum))
Next intNum
End If
‘return result
MultiSubst = strInput
Exit Function
Err_Handler:
MultiSubst = varInput
End Function