Greetings,

I need to remove non numeric characters from a text string in an
automatic input into targeted cells.

I am loading these cells with telephone numbers and extensions. They
have different formats and I want to normalized them to a single
format (one for the phone and 1 for the extension if there is one)

The format I am looking for is:

Phone: (###) ###-####
Extension: Ext. ######

There are a couple of problems with these formats.
1) Phone numbers without area code give me () ###-####.
2) The extension number format only kicks in if there are only
numbers.

This is the code that I am running in the Worksheet_Change() event:
__________________________________________________ _________________
Private Sub Worksheet_Change(ByVal Target As Range)
Dim bFlag As Boolean
Dim S1 As String, s2 As String

S1 = Target.Value
If Target.Count 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Len(S1) = 0 Then Exit Sub
Select Case Target.Column
Case 19, 21, 37, 39, 41, 43, _
45, 47, 49, 51, 53, 55 'Telephone format
If Not Len(S1) = 10 Then Exit Sub
s2 = Replace(S1, "(", "")
s2 = Replace(s2, ")", "")
s2 = Replace(s2, ".", "")
s2 = Replace(s2, " ", "")
s2 = Replace(s2, "-", "")
s2 = Replace(s2, "_", "")
s2 = Format((S1), "(###) ###-####")
bFlag = S1 < s2
If bFlag Then
On Error GoTo EndIt
Application.EnableEvents = False
Target.Value = s2
End If
Case 20, 22, 38, 40, 42, 44, _
46, 48, 50, 52, 54, 56 'Telephone extension format
s2 = Replace(LCase(S1), "ext", "")
s2 = Replace(LCase(S1), "x", "")
s2 = Replace(S1, "(", "")
s2 = Replace(s2, ")", "")
s2 = Replace(s2, ".", "")
s2 = Replace(s2, " ", "")
s2 = Replace(s2, "-", "")
s2 = Replace(s2, "_", "")
s2 = Format((S1), "Ext #####")
bFlag = S1 < s2
If bFlag Then
On Error GoTo EndIt
Application.EnableEvents = False
Target.Value = s2
End If
Case Else
Exit Sub
End Select

EndIt:
If bFlag Then Application.EnableEvents = True

End Sub
__________________________________________________ _________________

Is there anyway to fix these two problems?

Any help will be appreciated.

Thanks for looking at my challenge.

-Minitman