You want to replace the cell with what?

You want to remove everything or just anything that isn't 0-9?

Which is it?

You could use this UDF to remove all but 0-9

Function RemAlpha(str As String) As String
'Remove Alphas from a string
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function

Usage is: =remalphas(cellref)

Or run this macro on a selected range..................

Sub RemoveAlphas()
' Remove alpha characters from all strings.
' except for decimal points
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 'edit to suit
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub


Gord Dibben MS Excel MVP



On Sat, 20 Sep 2008 20:52:00 -0700, dk wrote:

I am trying to find all number entries that contain any character other then
0-9 and
replace the cell and remove everthing from those cells