Here are a couple of sample functions to help you clean up phone numbers.
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