Format Phone Number

From Starfish ETL
Revision as of 17:14, 12 October 2015 by Jkuehlthau (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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