to find numbers and delete

[vikram]

Hi ALL,
can we have macro which finds in a column if there is a cell whic
contains number along with text with it amd upon finding such cel
clears that cell but not delete tht cell.

like if I have in a cell if i have 10000Account, it clears that cel
and if i have VikramAccount it doesnt clear tht cell

In other words if that cell has text and number only then it clear
that cell

thank u guy

 

[vikram]

the text remains same and that is Account.......only the number in fron
of tis textr change

 

[Gord Dibben]

vikram

Which is it? Clear the contents of the cell or delete the numerics from the
cell?

1. Clear contents entirely.

Sub Clear_All()
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If (Mid(rngR.Value, intI, 1)) Like "[0-9]" Then
rngR.ClearContents
strNotNum = Mid(rngR.Value, intI, 1)
End If
Next intI
Next rngR
End Sub

2. Clear just the numerics.

Sub RemoveNums()
'' Remove numeric characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Not (Mid(rngR.Value, intI, 1)) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

Gord Dibben Excel MVP