Need help clean strings

[fredrerik84]

Hello great community. I have some strings I need to clean. Here is some samples:

leagueData('39290','ROUND 16','UZBEKISTAN','C','2016/2017','FA CUP'); return false;
leagueData('37935','ROUND 64','ARGENTINA','C','2016/2017','FA CUP'); return false;
leagueData('41003','STAGE 1','SOUTH AMERICA (CONMEBOL)','C','2016/2017','COPA SUDAMERICANA'); return false;
leagueData('37964','ROUND 16','ASIA (AFC)','C','2016/2017','AFC CHAMPIONS LEAGUE'); return false;

Any help with a vba code to get it to only return the text in red ?

The string is stored like this

Dim Leaguedata as string 
Dim r as Long 

and I have a loop which returns the results from above.

any help would be really appreciated

 

[JoeMo]

Is the red sub-string always followed by the last "')" (w/o the quote marks) in the string?

 

[fredrerik84]

Sorry in advance for long post , but I think yes ,

[TABLE="width: 44"]
<tbody>[TR]
[TD]leagueData('37949','PRIMERA DIVISION','ARGENTINA','L','2016/2017','PRIMERA DIVISION'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38182','APERTURA','COLOMBIA','L','2016/2017','PRIMERA A'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38959','APERTURA','PARAGUAY','L','2016/2017','PRIMERA DIVISION'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38133','SERIE A','BRAZIL','L','2016/2017','BRAZIL'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('39299','QUARTER FINALS','VENEZUELA','C','2016/2017','AP-PLAY OFFS'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38959','APERTURA','PARAGUAY','L','2016/2017','PRIMERA DIVISION'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38143','CANADIAN SOCCER LEAGUE','CANADA','L','2016/2017','CANADIAN SOCCER LEAGUE'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('37949','PRIMERA DIVISION','ARGENTINA','L','2016/2017','PRIMERA DIVISION'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('42777','QUARTER FINALS','COLOMBIA','C','2016/2017','AP2-PLAY OFFS'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('41128','ROUND 16','WORLD (FIFA)','C','2016/2017','U20 WORLD CUP KOREA REPUBLIC 2017'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('37964','ROUND 16','ASIA (AFC)','C','2016/2017','AFC CHAMPIONS LEAGUE'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('37964','ROUND 16','ASIA (AFC)','C','2016/2017','AFC CHAMPIONS LEAGUE'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38580','LIGA 1','INDONESIA','L','2016/2017','INDONESIA'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('41128','ROUND 16','WORLD (FIFA)','C','2016/2017','U20 WORLD CUP KOREA REPUBLIC 2017'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38703','RELEGATION PLAY OFF','ISRAEL','C','2016/2017','DIVISION 2 RELEGATION PLAY OFF'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('39290','ROUND 16','UZBEKISTAN','C','2016/2017','FA CUP'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('39290','ROUND 16','UZBEKISTAN','C','2016/2017','FA CUP'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('39051','PROFESSIONAL FOOTBALL LEAGUE URAL-POVOLZHYE','RUSSIA','L','2016/2017','PROFESSIONAL FOOTBALL LEAGUE'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('39290','ROUND 16','UZBEKISTAN','C','2016/2017','FA CUP'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('39051','PROFESSIONAL FOOTBALL LEAGUE URAL-POVOLZHYE','RUSSIA','L','2016/2017','PROFESSIONAL FOOTBALL LEAGUE'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38362','MEISTRILIIGA','ESTONIA','L','2016/2017','MEISTRILIIGA'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38630','GROUP STAGE','INTERNATIONAL TOURNAMENTS','C','2016/2017','INTERNATIONAL TOULON YOUTH FESTIVAL'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('39051','PROFESSIONAL FOOTBALL LEAGUE URAL-POVOLZHYE','RUSSIA','L','2016/2017','PROFESSIONAL FOOTBALL LEAGUE'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('39051','PROFESSIONAL FOOTBALL LEAGUE URAL-POVOLZHYE','RUSSIA','L','2016/2017','PROFESSIONAL FOOTBALL LEAGUE'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38362','MEISTRILIIGA','ESTONIA','L','2016/2017','MEISTRILIIGA'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38476','RELEGATION PLAY OFF','GERMANY','C','2016/2017','2. BUNDESLIGA RELEGATION PLAY OFF'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38362','MEISTRILIIGA','ESTONIA','L','2016/2017','MEISTRILIIGA'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38844','CHAMPION DECIDER','MOLDOVA','C','2016/2017','CHAMPION DECIDER'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38362','MEISTRILIIGA','ESTONIA','L','2016/2017','MEISTRILIIGA'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38630','GROUP STAGE','INTERNATIONAL TOURNAMENTS','C','2016/2017','INTERNATIONAL TOULON YOUTH FESTIVAL'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('37935','ROUND 64','ARGENTINA','C','2016/2017','FA CUP'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38721','SEMI FINALS','ITALY','C','2016/2017','SERIE B PROMOTION PLAY OFFS'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('37964','ROUND 16','ASIA (AFC)','C','2016/2017','AFC CHAMPIONS LEAGUE'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('39015','PREMIER DIVISION','REP. OF IRELAND','L','2016/2017','PREMIER DIVISION'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('38555','ROUND 16','ICELAND','C','2016/2017','FA CUP'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('37964','ROUND 16','ASIA (AFC)','C','2016/2017','AFC CHAMPIONS LEAGUE'); return false;[/TD]
[/TR]
[TR]
[TD]leagueData('41003','STAGE 1','SOUTH AMERICA (CONMEBOL)','C','2016/2017','COPA SUDAMERICANA'); return false;[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Untested, but try this. First select all the cells you want to extract from with your mouse, then run the code below. The extracts will appear in a newly added column adjacent to your selection.

Sub fredrerik()
'select all cells containing strings first, then run this macro
'output will be in a newly added adjacent column to the right of the selected column
Dim R As Range, e As Long, s As Long
Application.ScreenUpdating = False
Selection.Offset(0, 1).EntireColumn.Insert
For Each R In Selection
    On Error Resume Next
    e = InStrRev(R.Value, "')")
    s = InStrRev(R.Value, ",'")
    R.Offset(0, 1).Value = Mid(R.Value, s + 2, e - 2 - s)
    On Error GoTo 0
Next R
Selection.Offset(0, 1).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub

 

[fredrerik84]

I appreciate your help but the code can not be based on selection as these strings are generated in another code..

Best would be if I could somehow make it auto update the string

If I have the string in a variable or something

 

[JoeMo]

I appreciate your help but the code can not be based on selection as these strings are generated in another code..

Best would be if I could somehow make it auto update the string

If I have the string in a variable or something

Have your "other code" write the strings to a range, select the range, then call the code I posted and use the output as you wish (e.g. you could easily put it into an array).

Alternatively, post all of your "other code" and maybe someone can help you adapt the code I provided to you.

 

[fredrerik84]

It's actually a good option to just select that that range and then call your code will let you know how it works..

(it's a little late here, so will test tomorrow)

 

[fredrerik84]

It's actually a good option to just select that that range and then call your code will let you know how it works..

(it's a little late here, so will test tomorrow)

It worked as a dream, would it be possible to adjust it like this (from other code):

This is the code that generates the string:

Cells(R, "H").value = HTMLRows(i).Cells(4).getElementsByTagName("a")(0).getAttribute("*******")

After this my code loops the R var to generate a long list
would it be possible somehow to edit your code so that :

Cells(R, "H").value = "you string removal code"

Best regards

frederik

 

[JoeMo]

It worked as a dream, would it be possible to adjust it like this (from other code):

This is the code that generates the string:

Cells(R, "H").value = HTMLRows(i).Cells(4).getElementsByTagName("a")(0).getAttribute("*******")

After this my code loops the R var to generate a long list
would it be possible somehow to edit your code so that :

Cells(R, "H").value = "you string removal code"

Best regards

frederik

Maybe convert the code I gave you to a function you can use like below, but I have no way to test the subroutine part. When I compile it I get an error on this piece: HTMLRows(i) saying sub or function not defined. On the other hand, the function itself works fine on your sample strings when applied as a UDF directly on a worksheet.

Function fredrerikExtract(S As String) As String
Dim e As Long, st As Long
    On Error Resume Next
    e = InStrRev(S, "')")
    st = InStrRev(S, ",'")
    fredrerikExtract = Mid(S, st + 2, e - 2 - st)
    On Error GoTo 0
End Function
Sub test()
Cells(R, "H").Value = fredrerikExtract(HTMLRows(i).Cells(4).getElementsByTagName("a")(0).getAttribute("*******"))
End Sub

 

[Rick Rothstein]

Function fredrerikExtract(S As String) As String
Dim e As Long, st As Long
    On Error Resume Next
    e = InStrRev(S, "')")
    st = InStrRev(S, ",'")
    fredrerikExtract = Mid(S, st + 2, e - 2 - st)
    On Error GoTo 0
End Function
Sub test()
Cells(R, "H").Value = fredrerikExtract(HTMLRows(i).Cells(4).getElementsByTagName("a")(0).getAttribute("*******"))
End Sub

Here is another way to write this function...

[table="width: 500"]
[tr]
	[td]Function fredrerikExtract(S As String) As String
  Dim Parts() As String
  Parts = Split(S, "'")
  If UBound(Parts) > 0 Then fredrerikExtract = Parts(UBound(Parts) - 1)
End Function[/td]
[/tr]
[/table]