Public Sub Stripper()
''strip numbers or letters, user choice via inputbox
Dim myRange As Range
Dim Cell As Range
Dim myStr As String
Dim i As Integer
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address _
& "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
Which = InputBox("Strip Numbers - Enter 1" & vbCrLf & _
"Strip Letters - Enter 2")
If Which = 2 Then
For Each Cell In myRange
myStr = Cell.Text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) < 48) Or _
(Asc(UCase(Mid(myStr, i, 1))) > 57) Then
myStr = Left(myStr, i - 1) _
& " " & Mid(myStr, i + 1)
End If
Next i
Cell.Value = Application.Trim(myStr)
Next Cell
Selection.Replace What:=" ", _
Replacement:="", Lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
ElseIf Which = 1 Then
For Each Cell In myRange
myStr = Cell.Text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) < 65) Or _
(Asc(UCase(Mid(myStr, i, 1))) > 90) Then
myStr = Left(myStr, i - 1) _
& " " & Mid(myStr, i + 1)
End If
Next i
Cell.Value = Application.Trim(myStr)
Next Cell
Selection.Replace What:=" ", _
Replacement:="", Lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End If
End If
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub