Extracting a sub string from a main string

[chrisbrookeanna]

I have a field with data type string such as
"[25] Example"

I just want to extract any number that appears within the [] brackets. Any Ideas?

 

[Mile-O]

Assuming every field contains a [ first and then a ]

Public Function Extraction(strField As String) As Long
    Dim intPosA As Integer, intPosB As Integer
    intPosA = InStr(1, strField, "[")
    intPosB = InStr(1, strField, "]")
    Extraction = Mid(strField, intPosA + 1, intPosB - intPosA - 1)
End Function

 

[Kodo]

I don't want to step on toes but using Regular Expressions for pattern matching like this is much more resource efficient.

Here's one that will get the first match in your string of [0-9] where 0-9 is any number of any length. The value returned is also a string but you can convert that to the length number data type that you need,i.e. Integer, Long, Double etc..

Just pass your text box variable to the function.

NOTE: you will need to set a reference to MS VBScript Regular Expressions 5.5

Function GetNum(ByVal MyVar As String) As String



Set objregexp = New RegExp
        objregexp.pattern = "\[([0-9]*)\]"
        objregexp.Global = True
        objregexp.ignorecase = True
        
Set Matches = objregexp.Execute(MyVar)
        MyNum = Matches(0)

GetNum = objregexp.Replace(MyNum, "$1")

Set Matches = Nothing
Set objregexp = Nothing
End Function

 

[Mile-O]

Just pass your text box variable to the function.

And define Matches, and MyNum, and objRegNew, etc.

Option Explicit is the best.

 

[Kodo]

And define Matches, and MyNum, and objRegNew, etc.

Option Explicit is the best.

yes of course! I always leave that out for brevity when posting in hopes that people would know better. But I guess I should know better than to hope they would ..lol.

 

[Mile-O]

I think the easiest solutions are always the best when someone is new to VB. If they just copy and paste solutions then they will continue to ask questions but, with the standard functions, they have the chance to explore them in the help files much easier, and can learn from them. If they want to get advanced then, having taken some initiative, it shouldn't pose a problem to learn.

 

[Kodo]

here I go hoping again that it would spark some interest in trying to figure out how it works.. but, point taken.

 

[raskew]

yes of course! I always leave that out for brevity when posting in hopes that people would know better. But I guess I should know better than to hope they would ..lol.

Regular Expressions is not something native to VBA, but how 'bout pointing readers to a site where they could learn more about it?

Bob

 

[Kodo]

The following link is for VBScript but please note that the syntax is 99% the same for VBA. Where you see anything that says RESPONSE.WRITE in the article, you can use DEBUG.PRINT or even an MSGBOX. That's pretty much the only difference.

When using RegExp's, you have to include the VBScript reference for regular expressions version 5.5.

http://www.4guysfromrolla.com/webtech/090199-1.shtml

Have fun!