Trim some Url data

[Sharid]

I am trying to pull this from a url, but I am having problems triming it. I could do it with text to column by spliting via "=" but that splits it in several places. I have some urls in Sheet1 A and would like the results to go into B

Get this J4FE46MQDN8G7MECKSJ4
I have removed the https from the url as it kept converting it to a link

://amazon.co.uk/Amazon-co-uk-Gift-Card-Greeting-Abstract/dp/B07R3WMZWY/ref=zg_bsms_gift-cards_home_1/259-3701177-2096712?_encoding=UTF8&psc=1&refRID=J4FE46MQDN8G7MECKSJ4;3 Mb;DT: 103741.3 ms

 

[YasserKhalil]

Try this code

Sub Test()
    Dim s As String
    s = "://amazon.co.uk/Amazon-co-uk-Gift-Card-Greeting-Abstract/dp/B07R3WMZWY/"
    s = s & "ref=zg_bsms_gift-cards_home_1/259-3701177-2096712?_encoding=UTF8&psc=1&refRID=J4FE46MQDN8G7MECKSJ4;3 Mb;DT: 103741.3 ms"
    Debug.Print Split(Split(s, "refRID=")(1), ";")(0)
End Sub

 

[Sharid]

Thanks YasserKhalil

How can I get this to work for a list of urls in A1

 

[Sharid]

I tried this not working, I don't fully understand Trim

=MID(A1,1,IFERROR(FIND("&refRID=",A1),FIND(";",A1))+0)

 

[Fluff]

Is the part you want always followed by a semi colon?

 

[Sharid]

I am after the red part, in all the urls in a list in A1

Get this J4FE46MQDN8G7MECKSJ4
I have removed the https from the url as it kept converting it to a link

://amazon.co.uk/Amazon-co-uk-Gift-Card-Greeting-Abstract/dp/B07R3WMZWY/ref=zg_bsms_gift-cards_home_1/259-3701177-2096712?_encoding=UTF8&psc=1&refRID=J4FE46MQDN8G7MECKSJ4;3 Mb;DT: 103741.3 ms

 

[YasserKhalil]

Can you show us the list of urls in A1? Do you have all the URLs in one cell? If yes, how do you separate each URL from the other?

 

[Fluff]

Can you please answer my question?

 

[YasserKhalil]

Try using regex like that

Sub Test()
    Dim a(), m As Object, txt As String, i As Long, ii As Long
    txt = Range("A1").Value
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "refRID=(.*);\d"
        If .Test(txt) Then
            ReDim a(1 To .Execute(txt).Count, 1 To 5)
            For i = 0 To .Execute(txt).Count - 1
                Set m = .Execute(txt)(i).SubMatches
                For ii = 0 To m.Count - 1
                    a(i + 1, ii + 1) = m(ii)
                Next ii
            Next i
        End If
    End With
    Range("G1").Resize(UBound(a, 1), UBound(a, 2)) = a
End Sub

 

[Sharid]

Sorry my fault I had the wrong set of urls, they will look like this and I need the bit in red, every thing between the semi colon and the "&" they will all be between the semi colon and "&"

/gp/help/seller/at-a-glance.html/ref=dp_merchant_link?ie=UTF8&seller=A23DQHPJ8M9RZ3&isAmazonFulfilled=10

My fault for posting the wrong urls first