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 FormatPhone(phone)
	dim wPhone
	dim wPos
	dim wPhoneNbr
	dim wPhoneExt
	wPhone = phone
	wPos = InStr(1, wPhone, "+")
	if wPos > 0 then
		FormatPhone = phone
	else
		wPos = InStr(1, LCase(wPhone), "x")
		if wPos > 0 then
			wPhoneNbr = Trim(Left(wPhone, (wPos - 1)))
			wPhoneExt = Trim(Right(LCase(wPhone), (Len(wPhone) - (wPos) + 1)))
		else
			wPhoneNbr = Trim(wPhone)
			wPhoneExt = ""
		end if
		if IsNumeric(wPhoneNbr) then
			if Len(wPhoneNbr) = 7 then
				FormatPhone = Left(wPhoneNbr, 3) & "-" & Right(wPhoneNbr, 4) & " " & wPhoneExt
			else
				if Len(wPhoneNbr) = 10 then
					FormatPhone = Left(wPhoneNbr, 3) & "-" & Mid(wPhoneNbr, 4, 3) & "-" & Right(wPhoneNbr, 4) & " " & wPhoneExt
				else
					if Len(wPhoneNbr) = 11 then
						FormatPhone = Left(wPhoneNbr, 1) & "-" & Mid(wPhoneNbr, 2, 3) & "-" & Mid(wPhoneNbr, 5, 3) & "-" & Right(wPhoneNbr, 4) & " " & wPhoneExt
					else
						FormatPhone = phone
					end if
				end if
			end if	     
		else
			FormatPhone = phone
		end if
	end if
End Function