M
Minitman
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
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