Placeholder in function for selected cells

[Sayth]

Hi

I am having a basic problem. I am trying to create a udf. Its very basic I just want a udf for converting a cell range well not converting removing non numeric characters and leaving only numbers.

I can't figure how to put a placeholder in the udf to stand for whatever cell range selected by user.

This is where I am at.

Public Function RegExNumbers() As Integer
i = Selected cell range
RegExpSubstitute(i,"[^a-zA-Z0-9]+","")
End Function

this is the resources am I using

http://www.tmehta.com/regexp/examples.htm

http://www.mvps.org/dmcritchie/excel/install.htm#install

Edit: This is what some of the example data looks like, I just want the numbers.
<table width="64" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <tbody><tr style="height: 26.25pt;" height="35"> <td class="xl65" style="height: 26.25pt; width: 48pt;" width="64" height="35">[ 4]</td> </tr> <tr style="height: 39pt;" height="52"> <td class="xl65" style="height: 39pt; border-top: medium none; width: 48pt;" width="64" height="52">[ 1]</td> </tr> <tr style="height: 26.25pt;" height="35"> <td class="xl65" style="height: 26.25pt; border-top: medium none; width: 48pt;" width="64" height="35">[ 9]</td> </tr> <tr style="height: 26.25pt;" height="35"> <td class="xl65" style="height: 26.25pt; border-top: medium none; width: 48pt;" width="64" height="35">[ 7]</td> </tr> <tr style="height: 26.25pt;" height="35"> <td class="xl65" style="height: 26.25pt; border-top: medium none; width: 48pt;" width="64" height="35">[ 6]</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none; width: 48pt;" width="64" height="20">[10]</td> </tr> <tr style="height: 26.25pt;" height="35"> <td class="xl65" style="height: 26.25pt; border-top: medium none; width: 48pt;" width="64" height="35">[11]</td> </tr> <tr style="height: 26.25pt;" height="35"> <td class="xl65" style="height: 26.25pt; border-top: medium none; width: 48pt;" width="64" height="35">[ 8]</td> </tr> <tr style="height: 26.25pt;" height="35"> <td class="xl65" style="height: 26.25pt; border-top: medium none; width: 48pt;" width="64" height="35">[ 5]</td> </tr> <tr style="height: 26.25pt;" height="35"> <td class="xl65" style="height: 26.25pt; border-top: medium none; width: 48pt;" width="64" height="35">[ 2]</td> </tr> </tbody></table>

 

[AlphaFrog]

This will integer-ize one cell or text string e.g:
=RegExNumbers(A1)

It's not clear if you want the UDF to work on an entire range at once and concatenate the result or what. This just works on the one cell.

#Const LateBind = True

Function RegExNumbers(ReplaceIn) As Integer
    #If Not LateBind Then
        Dim RE As RegExp
        Set RE = New RegExp
    #Else
        Dim RE As Object
        Set RE = CreateObject("vbscript.regexp")
    #End If
    RE.Global = True
    RE.Pattern = "[^a-zA-Z0-9]+"
    RegExNumbers = RE.Replace(ReplaceIn.Value, "")
End Function

If you just want to remove the brackets and don't have any other characters to worry about, you could use a a more simple, albeit less versatile, UDF code like this...

=STRIPBRACKETS(A1)

Function STRIPBRACKETS(ReplaceIN) As Integer

    STRIPBRACKETS = Trim(Replace(Replace(ReplaceIN, "[", ""), "]", ""))

End Function

Or just this formula would remove the brackets as well.

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]",""))*1

 

[Sayth]

Thanks for looking at it for me.

i was trying to having udf work on a user defined range whether it be one cell or twenty.

I envisage the user selecting range and then running function. Or I might reference the function from a macro later.

 

[AlphaFrog]

A user wouldn't select a range and run a UDF. They would run a macro.

UDFs are formula based operations. They return a result to the cell that has the UDF as a formula.

With the code below, the user would run the macro Integerize_Selection to run the private function RegExNumbers on each cell in their selection.

#Const LateBind = True

Private Function RegExNumbers(ReplaceIN As String)
    Dim vResult As Variant
    #If Not LateBind Then
        Dim RE As RegExp
        Set RE = New RegExp
    #Else
        Dim RE As Object
        Set RE = CreateObject("vbscript.regexp")
    #End If
    RE.Global = True
    RE.Pattern = "\D"
    RegExNumbers = RE.Replace(ReplaceIN, "")
End Function

Sub Integerize_Selection()

    Dim cell As Range
    
    For Each cell In Selection
        cell.Value = RegExNumbers(cell.Text)
    Next cell

End Sub

 

[Sayth]

Wow that runs really cool. Perfect thank you indeed.