Need help with a tricky string

[fredrerik84]

Hi Guys Ive been away from this forum for a couple of weeks as most of my work I have been able to handle after all tips and trick I have learned he.

But now I run into another problem: I try to Clean a string like this:

If InStr(Venue, "Venue") = 0 Then
    Cells(rr, "AJ").Value = "Venue: Unknown"
Else
    Cells(rr, "AJ").Value = Venue
End If
                         
If InStr(Venue, "Referee") <> 0 Then
     Pos = InStr(1, Venue, "Referee")
     Venue = Left(Venue, Pos - 2)
      Cells(rr, "AJ").Value = Venue
End If

This code have been enough to get my desired results for the most part but just now the string returned a unexpected result which my code cant handle:

First leg (2-1), agg. (3-3). THE NEW SAINTS AET. Venue: Estadio Algarve, Faro Loule. MATCH SUMMARY : Europa earned 1st two legged victory in last seasons UEFA Europa League. Europa lifted 1st Gibraltar league title in 65 years in 2016/17. TNS reached 2nd qualifying round in each of last 5 seasons. Winner face Croatian title holders Rijeka in 2nd round.

<tbody>
</tbody>

Could anyone please help me update my code so that the above string gets reduced to:

"Venue: Estadio Algarve, Faro Loule."

 

[nardagus]

Hello,

Question is if this string you posted always looks the same:

"[Some text].Venue: [some text]. MATCH SUMMARY [some text]."
If yes you could do the same thing you did in IF with "Referee". Just find starting position of Venue and assign it to a var (i.e. venpos). Then find starting position of MATCH SUMMARY and assign it to a var (i.e. matchpos) then you could write:

If InStr(Venue, "Venue") = 0 Then
    Cells(rr, "AJ").Value = "Venue: Unknown"
Else
    Cells(rr, "AJ").Value = mid(Venue, venpos, matchpos -2)
End If

 

[fredrerik84]

Hi thanks for your reply.
unfortunately this string does not always look the same. could i somehow use your code like thi:

If InStr(Venue, "leg ") < 0 Then
Cells(rr, "AJ").Value = mid(Venue, venpos, matchpos -2)
end if

Also what is the best way to combine the with the code I pasted earlier . Im quite sure there will be more unexpected returns from this string.

 

[fredrerik84]

Hi thanks for your reply.

unfortunately this string does not always look the same. could i somehow use your code like thi:

If InStr(Venue, "leg ") < 0 Then
Cells(rr, "AJ").Value = mid(Venue, venpos, matchpos -2)
end if

Also what is the best way to combine the with the code I pasted earlier . Im quite sure there will be more unexpected returns from this string.

I have tested this a little now as you suggested, but for some reason "matchpos returns 86 , not sure why so this is what I get returned:

"Venue: Estadio Algarve, Faro Loule. MATCH SUMMARY : Europa earned 1st two legged vic"

<tbody>
</tbody>

and here is my updated code:

If InStr(Venue, "First leg") <> 0 Then
   venpos = InStr(Venue, "Venue")
   matchpos = InStr(Venue, "MATCH")
   MsgBox matchpos
   Venue = Mid(Venue, venpos, matchpos - 2)
   Cells(rr, "AJ").Value = Venue
End If

 

[nardagus]

My bad. I didn't take into account that for Venue instr returns value greater than 1.

Change this:

Mid(Venue, venpos, matchpos - 2)

into

Mid(Venue, venpos, matchpos - 1 - venpos)

Additional -1 is because resulting string has a space after last DOT. Unless that's not an issue then you can use this only:

Mid(Venue, venpos, matchpos - venpos)

As for your second question. If this string doesn't always look the same then this code won't work as intended. Maybe you need to organize data a bit differently? But for more suggestions I have to few info on what is your goal and how your data looks/can look like.

P.s. Your updated code gives you pos 1 (for "First leg" string), then it's ok. You can use it

 

[fredrerik84]

My bad. I didn't take into account that for Venue instr returns value greater than 1.

Change this:

Mid(Venue, venpos, matchpos - 2)

into

Mid(Venue, venpos, matchpos - 1 - venpos)

Additional -1 is because resulting string has a space after last DOT. Unless that's not an issue then you can use this only:

Mid(Venue, venpos, matchpos - venpos)

As for your second question. If this string doesn't always look the same then this code won't work as intended. Maybe you need to organize data a bit differently? But for more suggestions I have to few info on what is your goal and how your data looks/can look like.

P.s. Your updated code gives you pos 1 (for "First leg" string), then it's ok. You can use it

Hi thanks for your reply , This last part of the code was driving me crazy , I guess its easy when you know what you are doing really appreciate your help.

"Maybe you need to organize data a bit differently?"

- Unfortunately I have no control over what is stored in this string , as this info is pulled from an web page.

But have tested all my 150 rows and so far all looks great

 

[nardagus]

I'm glad I could help.

 

[fredrerik84]

I'm glad I could help.

Do you have time for maybe a quick follow up question. as I got another string that my code dont have covered :/

string:

Missed Penalty for PORTUGAL at 17th min. by SILVA A. Venue: Spartak Stadium, Moscow.

<tbody>
</tbody>

I need everything to the right for "venue" Venue: Spartak Stadium, Moscow.

Maybe if you give another sample I will understand exactly how to use left,mid,right correctly

Edit: Nevermind handled it myself:

                 If InStr(Venue, "Missed Penalty") <> 0 Then
                            venpos = InStr(Venue, "Venue")
                            matchpos = Len(Venue)
                            Venue = Mid(Venue, venpos, matchpos - 1 - venpos)
                            Cells(rr, "AJ").Value = Venue
                         End If

 

[nardagus]