Format Phone Number

Format Phone Number

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

	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
			'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)))
				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
				ScriptedField = oldphone
			end if
		end if
    end if
End Function
    • Related Articles

    • Twilio Connector

      Twilio Authentication The use of the Twilio connector requires a Twilio account. Instead of a username and password, you sign into the API with using your Account SID and a secret Auth Token that is assigned to you. This can be retrieved from the ...
    • Zoho CRM Connector

      Additional Documentation This Connector is based on the Zoho CRM ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: Authenticating to Zoho CRM The provider is ...
    • Act-On Connector

      Connecting to Act-On Please read the instructions to obtain your API Key and API call limitations here: Please supply the username, password and API Key for Act-On in the Connector window: Contacts To read ...
    • SugarCRM SOAP Connector

      SugarCRM SOAP Origin Note that the tab in StarfishETL is labelled simply "SugarCRM". Sample Origin Filters Mapping Attachments In Sugar, Attachments are stored as Notes. To attach a file to a Note, simply run and Insert, Update or Update/Insert Stage ...
    • Scripting Class Object

      From within VBScript operations (ScriptedVariable, VBScriptProcedure, ScriptedField) in additional to normal VBScript functions, the developer has access to a custom “Starfish” class. The tables below define its usage. This class may also be used ...