If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find Replace Wildcard
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 |
#2
|
|||
|
|||
Find Replace Wildcard
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 |
#3
|
|||
|
|||
Find Replace Wildcard
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 |
#4
|
|||
|
|||
Find Replace Wildcard
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 |
#5
|
|||
|
|||
Find Replace Wildcard
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 |
#6
|
|||
|
|||
Find Replace Wildcard
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 |
#7
|
|||
|
|||
Find Replace Wildcard
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 |
#8
|
|||
|
|||
Find Replace Wildcard
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 |
#9
|
|||
|
|||
Find Replace Wildcard
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 |
#10
|
|||
|
|||
Find Replace Wildcard
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 |
Thread Tools | |
Display Modes | |
|
|