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

Hi,

This is confusing!

I am trying to find all number entries that contain any character other then
0-9

If you are looking for number cells only what else would be in the cell
other then 0 - 9?

Please provide some example of what you are looking for.

Mike

"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

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

remove everything if there is any character other then numeric in that cell

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

Sub Remove_If_Not_Nums()
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

rngR.Value = ""
End If
Next
Next

End Sub


Gord

On Sun, 21 Sep 2008 09:32:01 -0700, dk wrote:

remove everything if there is any character other then numeric in that cell

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

we get runtime error 104 at
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

"Gord Dibben" wrote:

Sub Remove_If_Not_Nums()
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

rngR.Value = ""
End If
Next
Next

End Sub


Gord

On Sun, 21 Sep 2008 09:32:01 -0700, dk wrote:

remove everything if there is any character other then numeric in that cell

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

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/
....then use this formula

=REGEX.SUBSTITUTE(A1,"\D+")


"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

this does not empty the cell only removes the none numeric characters

"Teethless mama" wrote:

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/
...then use this formula

=REGEX.SUBSTITUTE(A1,"\D+")


"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

I would assume your selection contained no text value cells.

Sub Remove_If_Not_Nums()
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
On Error GoTo endit
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
rngR.Value = ""
End If
Next
Next
Exit Sub
endit:
MsgBox "only numbers or blanks in selection"
End Sub


Gord

On Sun, 21 Sep 2008 10:46:00 -0700, dk wrote:

we get runtime error 104 at
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

"Gord Dibben" wrote:

Sub Remove_If_Not_Nums()
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

rngR.Value = ""
End If
Next
Next

End Sub


Gord

On Sun, 21 Sep 2008 09:32:01 -0700, dk wrote:

remove everything if there is any character other then numeric in that cell

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

Can you give us a clear for bth keeping the numeric part of the cell or
removing the whole cell meantme no success

"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