converting value to phone number format

[austin350s10]

Does anyone know how to use vba to format a phone number to the correct format? For some reason when a user enters a phone number with this format: (###)###-#### my script will not change the value to the correct format of: (###) ###-####. The only format difference is the space between the area code and the start of the phone number. The code below tests as true and executes the 2nd line of code but its almost as if the format function does not recognize the space. Do I need to use a special character or something to represent a space when formatting? Or am i doing this completely the wrong way?

not working:

If Len(me.txtPhone.Value) = 13 And me.txtPhone.Text Like "(###)###-####" Then
me.txtPhone.Value = Format(me.txtPhone.Text, "(###) ###-####")
End If

working:

If Len(me.txtPhone.Value) = 10 And me.txtPhone.Text Like "##########" Then
me.txtPhone.Value = Format(me.txtPhone.Text, "(###) ###-####")
End If

 

[jeffmb]

I believe that the value is being viewed as text and therefore requires string manipulation. Try:

    If Len(Me.txtPhone.Value) = 13 And Me.txtPhone.Text Like "(###)###-####" Then
        Me.txtPhone.Value = Left(Me.txtPhone.Value, 5) & " " & Right(Me.txtPhone.Value, 8)
    End If