smck

Macro..........

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
End If
End If
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben Excel MVP

On Sun, 24 Apr 2005 19:49:01 -0700, "smck"
wrote:

I sent this before but not sure if it was accepted.

I have a worksheet with some cells containing values and some containing
values and text. e.g. 1234 or T 1234 or Test 1234. I need a macro or Formula
to remove only the text from these cells. I tried creating a formula using
ISTEXT function but I am missing something and its not working.

Help and Thanks.
smck