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