Format Phone Number: Difference between revisions

From Starfish ETL
Jump to navigation Jump to search
No edit summary
No edit summary
 
Line 1: Line 1:
Here are a couple of sample functions to help you clean up phone numbers.
Here are a couple of sample functions to help you clean up phone numbers.
<source lang="vb">
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
</source>


<source lang="vb">
<source lang="vb">

Latest revision as of 22:14, 12 October 2015

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