Today, for my customer’s app, I wrote some VBA code in an after update routine of a textbox to strip out non-numeric characters and reformat them into a phone number format the customer wanted.

Private Sub employee_phone_number_AfterUpdate() 
    If IsPhoneNumberThere Then 
        FormatPhoneNumber 
    End If 
End Sub 

Private Function IsPhoneNumberThere() As Boolean 
    Dim retVal As Boolean 
    If Len(HALutil.RegExReplace( "[^0-9]",Me.employee_phone_number.Value, "")) = 10 Then retVal = True 
    IsPhoneNumberThere = retVal 
End Function 

Private Sub FormatPhoneNumber() 
    Dim RawNine As String 
    RawNine = HALutil.RegExReplace( "[^0-9]",Me.employee_phone_number.Value, "") 
    RawNine = "(" & Left$(RawNine,3) & ") " & Mid$(RawNine,4,3) & "-" & Right$(RawNine,4) 
    Me.employee_phone_number.Value = RawNine 
End Sub 

And here is our RegExReplace function I’m using which we shamelessly copied and pasted from Mike Wolfe:

'---------------------------------------------------------------------------------------
' Procedure : RegExReplace
' Author    : Mike Wolfe <mike@nolongerset.com>
' Date      : 11/4/2010
' Source    : https://nolongerset.com/now-you-have-two-problems/
' Purpose   : Attempts to replace text in the TextToSearch with text and back references
'               from the ReplacePattern for any matches found using SearchPattern.
' Notes     - If no matches are found, TextToSearch is returned unaltered.  To get
'               specific info from a string, use RegExExtract instead.
'>>> RegExReplace("(.*)(\d{3})[\)\s.-](\d{3})[\s.-](\d{4})(.*)", "My phone # is 570.555.1234.", "$1($2)$3-$4$5")
'My phone # is (570)555-1234.
'---------------------------------------------------------------------------------------
'
Public Function RegExReplace(SearchPattern As String,TextToSearch As String,ReplacePattern As String,_ 
   Optional GlobalReplace As Boolean = True,_ 
   Optional IgnoreCase As Boolean = False,_ 
   Optional MultiLine As Boolean = False) As String 
   
   Dim RE As Object 
   Set RE = CreateObject( "vbscript.regexp") 
   With RE 
      .MultiLine = MultiLine 
      .Global = GlobalReplace 
      .IgnoreCase = IgnoreCase 
      .Pattern = SearchPattern 
   End With 
    
   RegExReplace = RE.Replace(TextToSearch,ReplacePattern) 
End Function 

The reason I wrote an after update routine in the field is so it would happen as soon as they finished and left the field. I didn’t use an input mask because that stores the number only in the field, not with the formatting. That would then require me to find all the places it’s currently displayed, exported, etc. and make sure I used the format command on it to put it into the desired format.

Thankfully, the client is pretty consistent and has entered all the numbers either already in the format he wanted or with dashes between. This allowed me to use a pretty simple, albeit long, replacement strategy in a SQL query. So here is the query I’m running against the SQL server to change all the existing data for the field:

'UPDATE [employees] SET
'[employee_phone_number] =
'CONCAT(
''(',
'LEFT(
' REPLACE(REPLACE(REPLACE(REPLACE([employee_phone_number],'(',''),')',''),' ',''),'-','')
',3),') ',
'SUBSTRING(
' REPLACE(REPLACE(REPLACE(REPLACE([employee_phone_number],'(',''),')',''),' ',''),'-','')
',4,3),'-',
'RIGHT(
' REPLACE(REPLACE(REPLACE(REPLACE([employee_phone_number],'(',''),')',''),' ',''),'-','')
',4)
')
 sqlDB.Execute  "UPDATE [employees] SET " & vbCrLf & _ 
      "[employee_phone_number] = " & vbCrLf & _ 
      "CONCAT( " & vbCrLf & _ 
       "'(', " & vbCrLf & _ 
       "LEFT( " & vbCrLf & _ 
       " REPLACE(REPLACE(REPLACE(REPLACE([employee_phone_number],'(',''),')',''),' ',''),'-','') " & vbCrLf & _ 
       ",3),') ', " & vbCrLf & _ 
       "SUBSTRING( " & vbCrLf & _ 
       " REPLACE(REPLACE(REPLACE(REPLACE([employee_phone_number],'(',''),')',''),' ',''),'-','') " & vbCrLf & _ 
       ",4,3),'-', " & vbCrLf & _ 
       "RIGHT( " & vbCrLf & _ 
       " REPLACE(REPLACE(REPLACE(REPLACE([employee_phone_number],'(',''),')',''),' ',''),'-','') " & vbCrLf & _ 
       ",4) " & vbCrLf & _ 
       ")"