Trim some Url data

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,049
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

YasserKhalil

Well-known Member
Joined
Jun 24, 2010
Messages
815
Try this code
VBA 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

Well-known Member
Joined
Apr 22, 2007
Messages
1,049
Office Version
  1. 2016
Platform
  1. Windows
Thanks YasserKhalil

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

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,049
Office Version
  1. 2016
Platform
  1. Windows
I tried this not working, I don't fully understand Trim
VBA Code:
=MID(A1,1,IFERROR(FIND("&refRID=",A1),FIND(";",A1))+0)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,049
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jun 24, 2010
Messages
815

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Can you please answer my question?
 

YasserKhalil

Well-known Member
Joined
Jun 24, 2010
Messages
815
Try using regex like that
VBA Code:
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

Well-known Member
Joined
Apr 22, 2007
Messages
1,049
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,314
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top