Extract part of hyperlink?

[TheWennerWoman]

Hello,

I have been copying and pasting results from a backgammon site:

If I hover over the first hyperlink in the "Result" column, I see this (it will vary depending on who I was playing, of course):

https://zooescape.com/backgammon.pl?v=200&gid=5275015&p1=TheWennerWoman&p2=rabsnake

Is there a way of extracting the gid from here (game i.d)? In this case, I'd like to copy 5275015 and paste it into column J.

Many thanks for reading.

 

[Anthony47]

Maybe this user defined function:

Function GetGid(ByRef myRan As Range) As Variant
Dim myHL As String, gdStart As Long
'
myHL = myRan.Hyperlinks(1).Address
gdStart = InStr(1, myHL, "&gid=", vbTextCompare)
If gdStart > 0 Then
    gdEnd = InStr(gdStart, myHL, "&p1", vbTextCompare)
    GetGid = Mid(myHL, gdStart + 5, gdEnd - gdStart - 5)
End If
End Function

Put this code in a standard module of your vba project; then return to your worksheet, and in J2 insert this formula:

=GetGid(E3)

Copy down as needed

Bye

 

[Peter_SSs]

Would this user-defined function be what you want?

Function GameID(r As Range) As String
  GameID = Split(Split(r.Hyperlinks(1).Address, "gid=")(1), "&")(0)
End Function

TheWennerWoman.xlsm
	E	F	G	H	I	J
1
2	Won					5275015
Extract from hyperlink

Cell Formulas
Range	Formula
J2	J2	=GameID(E2)

 

[Anthony47]

Would this user-defined function be what you want?

I love Split, but on Monday morning I always look for something more complex

 

[Peter_SSs]

I love Split, but on Monday morning I always look for something more complex

Lucky it is Monday evening here.

 

[TheWennerWoman]

Thank you both, they both work but I can only mark one as the solution so Peter wins because his is ridiculously short and to the point. Brilliant both of you ?

 

[Anthony47]

I can only mark one as the solution so Peter wins because his is ridiculously short and to the point.

No doubt about the choice!

 

[Peter_SSs]

Thank you both, they both work ..

You're welcome. Glad we could help.