Format Phone Number
Here are a couple of sample functions to help you clean up phone numbers.
Function FormatPhone(ph) dim phone phone=ph phone = Replace(phone,"(","") phone = Replace(phone,")","") phone = Replace(phone," ","") phone = Replace(phone,".","") phone = Replace(phone,"-","") phone = lcase(phone) if Len(phone)=11 and Left(phone,1)="1" then phone=Mid(phone,2) if Len(phone)=10 then FormatPhone = "(" & mid(phone,1,3) & ")" & mid(phone,4,3) & "-" & mid(phone,7) elseif Len(phone)>10 then if mid(phone,11,1)="x" then FormatPhone = "(" & mid(phone,1,3) & ")" & mid(phone,4,3) & "-" & mid(phone,7) else FormatPhone = ph end if else FormatPhone = ph end if End Function
Function ScriptedField dim oldphone dim phone dim xPos dim plusPos dim extension oldphone="@@ORG:phone_office@@" phone=oldphone if len(phone) <> 0 then 'Remove all non-numberic characters except the x and + sign. With CreateObject("vbscript.regexp") .Pattern = "[^0-9x+]" .Global = True phone = .Replace(phone, "") End With plusPos = inStr(1,phone,"+") if plusPos > 0 then 'Ignore foreign phone numbers. ScriptedField = oldphone else 'break off the extension. xPos = inStr(1,LCase(phone),"x") if xPos > 0 then extension = Trim(Right(LCase(phone), (Len(phone) - (xPos) + 1))) phone = Trim(Left(phone, (xPos - 1))) Else extension = "" End If 'format non-foreign phone numbers and phone numbers without an extension. if Len(phone)=11 and Left(phone,1)="1" then phone = Mid(phone,2) ScriptedField = mid(phone,1,3) & "-" & mid(phone,4,3) & "-" & mid(phone,7) & extension elseif Len(phone)=10 then ScriptedField = mid(phone,1,3) & "-" & mid(phone,4,3) & "-" & mid(phone,7) & extension else ScriptedField = oldphone end if end if end if End Function