Using VBA to find text in a string

[Passman]

I have strings containing variable data of different lenghts. I would like to extract 1 piece of information from each string. The only constant is that it is always the data before 'Class'. I have looked at using the InStr function but I cannot work out if this can be used to find the data before the the string to search for. In the examples below......

£30000 added, 3yo plus, 1m 4f, Class 1, £17013 penalty, 5 ran
3yo plus, 7f, Class 6, 6 ran

..... the data I want is 1m 4f and 7f

I hope it is very simple but I can't see the wood for the trees on this one

Thanks in advance

 

[Norie]

InStr doesn't actually return part of a string, it returns the position of the string you are looking for in the string you are searching.

So using InStr we can find where Class is and then use Left to extract the text to the left.

strMeText = "£30000 added, 3yo plus, 1m 4f, Class 1, £17013 penalty, 5 ran
3yo plus, 7f, Class 6, 6 ran"
pos = InStr(1, strMeText, "Class")
 
Msgbox Left(strMeText, pos -1)

If you wanted to split the whole string out using the comma as a delimiter you could use Split.

 

[VoG]

Try like this

Sub test()
Dim i As Long, a As String
Dim s As String, X
s = "3yo plus, 7f, Class 6, 6 ran"
X = Split(s, ", ")
For i = 1 To UBound(X)
    If X(i) Like "Class*" Then
        a = X(i - 1)
        Exit For
    End If
Next i
MsgBox a
End Sub

 

[Passman]

Thanks Norie but that gives me everything before 'Class'. What I'm looking to extract is just the 1m 4f or 7f in the 2 examples I gave.

Perhaps InStr is the wrong way to go about it and there is a better way to find this information. Thanks for your help so far

 

[VoG]

See my post above.

 

[Passman]

Thanks VoG it works perfectly, trouble is I don't understand how!

 

[VoG]

Split creates a zero-based array (X) of the sections of your string, split at each comma.

The code then loops through that array checking each section for "Class". What you want is the element before that, X(i-1)

 

[Peter_SSs]

Another option would be to use a user-defined function like this.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> BeforeClass(<SPAN style="color:#00007F">ByVal</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        <SPAN style="color:#00007F">With</SPAN> RegEx<br>            .Global = <SPAN style="color:#00007F">True</SPAN><br>            .Pattern = "[^,]+(?=, *Class)"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> RegEx.test(s) <SPAN style="color:#00007F">Then</SPAN><br>        BeforeClass = Trim(RegEx.Execute(s)(0))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

Excel Workbook
	A	B
1
2	30000 added, 3yo plus, 1m 4f, Class 1, 17013 penalty, 5 ran	1m 4f
3	3yo plus, 7f, Class 6, 6 ran	7f
4	abc
5	def,Class	def
Extract Text

 

[Passman]

Thanks VoG for the explanation, it makes sense now. Thanks also Peter for the alternative, not now needed but I appreciate the help.

 

[Biz]

Another option would be to use a user-defined function like this.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.


Function BeforeClass(ByVal s As String) As String
Static RegEx As Object

If RegEx Is Nothing Then
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.Pattern = "[^,]+(?=, *Class)"
End With
End If
If RegEx.test(s) Then
BeforeClass = Trim(RegEx.Execute(s)(0))
End If
End Function

Excel Workbook
	A	B
1
2	30000 added, 3yo plus, 1m 4f, Class 1, 17013 penalty, 5 ran	1m 4f
3	3yo plus, 7f, Class 6, 6 ran	7f
4	abc
5	def,Class	def
Extract Text

Very nicely done mate.

Biz