RegExp: Del !Test(0-*9) with a WinXP .dll?

[jago25_98]

I can afford the £200 regex expansion for a simple job, and then have hassle of distributing that.

I need to turn:

fooTest1234foo

into -> Test1234

and things like

foofoo123Test23foofoo

into -> Test23

So that's a del,! and [0-9].
But the [0-9] number could be any length of digits.

I don't want to have to use the Right or Left functions and VBA if possible because that means learning VB. I can see the =LEFT or =RIGHT could be useful too. I'm looking into it, but it's a lot of work when I've never really programmed a decision before. Surely there's another way to do this?

I could do this easily with grep, cut, awk and sed, but I need this to work for colleges using Excel07 and WinXP.
Is there a .dll I can reference in my code (so Tools>references in VB doesn't need to be setup by the user) that can do it?

Thank you. I'm sure this has to be a common problem. I've noticed people asking similar questions, but none I've found cover matching digits [0-9] in combination with a text string pattern.

 

[RolfJ]

What language are you writing your code in?

 

[jago25_98]

Good point! Thank you for replying )))))

Just Visual Basic. Of the sort included with Excel.

I've started trying to do it manually by searching for `Test` and determining where that is mentioned in the cell. The only problem is that I then get stuck because the numbers after it can be any number of digits.

Any ideas anyone??

 

[RolfJ]

That would be VBA. I am not quite sure though what to make of your comment 'I don't want to have to use the Right or Left functions and VBA if possible because that means learning VB'. Disregarding that here is a function that would do what I think you want to get done:

Function ExtractTestAndNumbers(s As String)
    s = Mid(s, InStr(s, "Test"))
    Dim iPos As Integer
    iPos = 5
    Do
        If Not IsNumeric(Mid(s, iPos + 1, 1)) Then Exit Do
        iPos = iPos + 1
    Loop
    ExtractTestAndNumbers = Left(s, iPos)
End Function

Once you placed it in a standard VBA module you can either directly call it in your workbook by entering e.g.

= ExtractTestAndNumbers(B2)

assuming that cell B2 contains the string you would like to filter (e.g. fooTest1234foo) or call it from another VBA subroutine.

Hope this helped,
Rolf

 

[jago25_98]

Thanks I didn't know about IsNumeric, and wasn't too sure about how to use InStr so this is a great help to see how to do it. s = Mid(s, InStr(s, "Test")) gives me an error though. I thought it was because I am calling the function from a subroutine and that subroutine already uses s as a variable but I changed the variable and it's the same... hmm... invalid procedure call... it seems the s variable isn't being passed when the function is called with ExtractTestAndNumbers (C7)...hmm...

 

[pgc01]

I can afford the £200 regex expansion for a simple job, and then have hassle of distributing that.

Just curious. I guess you mean "I can't afford ...", but what do you mean? You don't have to pay to use RegEx's. Can you explain?

 

[jago25_98]

pgc01: http://www.aivosto.com/regexpr.html for VB costs £200. So instead I have to write RegExpressions manually as code.

I can't see what's wrong with

s = Mid(s, InStr(s, "Test"))

To be honest I don't fully get what it is doing; Middle of the string, find the pattern, but then it seems to be looping onto itself by calling the variable within itself.

 

[pgc01]

http://www.aivosto.com/regexpr.html for VB costs £200. So instead I have to write RegExpressions manually as code.

I see. I'm sure Aivosto's package is good, but why not use the RegExp's that come by default?

For ex.:

Function ExtractCode(s As String) As String
 
With CreateObject("VBScript.RegExp")
    .Pattern = ".*(Test\d+).*"
    ExtractCode = .Replace(s, "$1")
End With
End Function