D
dk
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
0-9 and
replace the cell and remove everthing from those cells
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
Mike H
Hi,
This is confusing!
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
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
G
Gord Dibben
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
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
D
dk
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
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
G
Gord Dibben
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
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
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
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
D
dk
we get runtime error 104 at
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
Gord Dibben said: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
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
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
T
Teethless mama
Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/
....then use this formula
=REGEX.SUBSTITUTE(A1,"\D+")
http://xcell05.free.fr/
....then use this formula
=REGEX.SUBSTITUTE(A1,"\D+")
D
dk
this does not empty the cell only removes the none numeric characters
G
Gord Dibben
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
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
we get runtime error 104 at
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
Gord Dibben said: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
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
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
D
dk
Can you give us a clear for bth keeping the numeric part of the cell or
removing the whole cell meantme no success
removing the whole cell meantme no success
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.