Format Phone Number: Difference between revisions
Jump to navigation
Jump to search
Jkuehlthau (talk | contribs) No edit summary |
Jkuehlthau (talk | contribs) No edit summary |
||
Line 31: | Line 31: | ||
<source lang="vb"> | <source lang="vb"> | ||
Function | Function ScriptedField | ||
dim | dim oldphone | ||
dim | dim phone | ||
dim | dim xPos | ||
dim | dim plusPos | ||
dim extension | |||
oldphone="@@ORG:phone_office@@" | |||
if | phone=oldphone | ||
if len(phone) <> 0 then | |||
'Remove all non-numberic characters except the x and + sign. | |||
if | 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 | 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 | else | ||
ScriptedField = oldphone | |||
end if | |||
end if | |||
end if | end if | ||
end if | |||
End Function | End Function | ||
</source> | </source> |
Revision as of 22:14, 12 October 2015
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