Using VBA to find text in a string

Passman · Sep 24, 2011

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

mikerickson · Sep 26, 2011

This non UDF can be put in a cell and dragged right to return the various values.

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($A1&REPT(",, Class",COLUMN(A1)), ", Class", REPT(" ",255), COLUMN(A1)), 255)), ",", REPT(" ",255)), 255))

Rick Rothstein · Sep 26, 2011

For anyone who might be interested, here is a non-RegExp version of Peter's user defined function (UDF) approach...

Code:

Function BeforeClass(S As String) As String
  Dim Parts() As String
  Parts = Split(S, "Class", , vbTextCompare)
  On Error Resume Next
  Parts = Split("," & Parts(0), ",")
  BeforeClass = Trim(Parts(UBound(Parts) + (UBound(Parts) >= 0)))
End Function

Peter_SSs · Sep 26, 2011

Deleted

Rick Rothstein · Sep 26, 2011

Peter_SSs said:
Deleted

Curious...

Although I don't know what you had posted, the fact that you did made me look at my UDF again and now I see it can be simplified ever so slightly (a protection I thought I needed appears not to be needed at all... the On Error statement, needed for another reason, takes care of it)...

Code:

Function BeforeClass(S As String) As String
  Dim Parts() As String
  Parts = Split(S, "Class", , vbTextCompare)
  On Error Resume Next
  Parts = Split("," & Parts(0), ",")
  BeforeClass = Trim(Parts(UBound(Parts) - 1))
End Function

Peter_SSs · Sep 26, 2011

Rick Rothstein said:
Curious...

Although I don't know what you had posted, ..

Rick

I hadn't thought of your approach and when I studied it I thought that I could see a way to eliminate both the Ubound>=0 check AND the OnError line.

However, after posting I discovered a (fairly obvious) circumstance where my code failed. Not enough testing before posting.

Rick Rothstein · Sep 26, 2011

Peter_SSs said:
I hadn't thought of your approach and when I studied it I thought that I could see a way to eliminate both the Ubound>=0 check AND the OnError line.

However, after posting I discovered a (fairly obvious) circumstance where my code failed. Not enough testing before posting.

Thanks for the explanation. Obviously, the code I posted didn't come to me all-at-once. First, I had the -1 in there, but after I added the "," concatenation, I found I needed to replace the -1 with +(UBound(Parts)>=0)) check to handle the data with no commas (your abc sample). Then, when I checked the UDF against empty cells, I found I needed the On Error statement. At the time, it didn't occur to me that the On Error statement would also remove the need for the +(UBound(Parts)>=0)) replacement of the -1... it took the relook your message prompted me to do to see that.

Peter_SSs · Sep 26, 2011

I did come up with an alternative without the OnError that I think works. But I can't see that it is any better, just different.

FWIW ..

Code:

Function BeforeClass(S As String) As String
    Dim Parts() As String
    S = "," & Left(S, InStr(S, "Class"))
    Parts = Split(S, "Class", , vbTextCompare)
    Parts = Split(Parts(0), ",")
    BeforeClass = Trim(Parts(UBound(Parts) - 1))
End Function

Peter_SSs · Sep 26, 2011

It's after midnight here and I'm not concentrating very well. It could be this couldn't it?

Code:

Function BeforeClass(S As String) As String
    Dim Parts() As String
    S = "," & Left(S, InStr(S, "Class"))
    Parts = Split(S, ",")
    BeforeClass = Trim(Parts(UBound(Parts) - 1))
End Function

Now, time for bed!

Rick Rothstein · Sep 26, 2011

Peter_SSs said:
It's after midnight here and I'm not concentrating very well. It could be this couldn't it?

Code:

Function BeforeClass(S As String) As String Dim Parts() As String S = "," & Left(S, InStr(S, "Class")) Parts = Split(S, ",") BeforeClass = Trim(Parts(UBound(Parts) - 1)) End Function

Now, time for bed!

I hadn't tested your original code... all I did was use the output table to design my code. However, in checking your original code, it does not produce any output for your sample "def,Class" data item even though your output table shows "def" as being outputted. The above quoted code that you just posted duplicates what your original code outputs... it also outputs an empty string for "def,Class". I designed my code to match your output table and so it outputs "def" for that item. However, in thinking about it some more, it occurs to me that my code over-reached a little bit... it returns "x" from a sample data item like ",x," (without the quotes, of course). So, when trying to patch for that, I decided to see if I could eliminate the On Error trap as well. Here is what I came up with (it duplicates the results of your output table and does not appear to return any false findings)...

Code:

Function BeforeClass(S As String) As String
  Dim Parts() As String
  If Len(S) = 0 Or InStr(1, S, "class", vbTextCompare) = 0 Then Exit Function
  Parts = Split("," & Split(S, "Class", , vbTextCompare)(0), ",")
  BeforeClass = Trim(Parts(UBound(Parts) - 1))
End Function

Peter_SSs · Sep 26, 2011

Rick, I know we are just picking around the edges of this but I am learning from it.

Rick Rothstein said:
However, in checking your original code, it does not produce any output for your sample "def,Class" data item even though your output table shows "def" as being outputted. The above quoted code that you just posted duplicates what your original code outputs... it also outputs an empty string for "def,Class".

I don't understand those results. They are not what I am getting and I cannot see how "def,Class" could produce a blank with either code.

Here are both my codes (renamed) and the results for some test data. I have shown the formulas for the "def,Class" row but each formula is copied from row 2 to row 10.

Function BeforeClassRX(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 BeforeClassRX = Trim(RegEx.Execute(S)(0)) End If End Function Function BeforeClassPS(S As String) As String Dim Parts() As String S = "," & Left(S, InStr(S, "Class")) Parts = Split(S, ",") BeforeClassPS = Trim(Parts(UBound(Parts) - 1)) End Function

Excel Workbook

A

B

C

1

BeforeClassRX

BeforeClassPS

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

asd,sdef,dff

5

def,Class

def

6

7

abc

8

,,Class, 6456

9

,xClass

10

,x,

Extract Text

You have got your function whittled down quite a way now, but I think you can go further.

I don't think you need the Len(S) = 0 test as the InStr test will return 0 for a zero length string.

Using VBA to find text in a string

Passman

Board Regular

mikerickson

MrExcel MVP

Excel Facts

Rick Rothstein

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Rick Rothstein

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Rick Rothstein

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

Rick Rothstein

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Using VBA to find text in a string

Board Regular

MrExcel MVP

Excel Facts

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP, Moderator

MrExcel MVP

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock