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

 

[Fluff]

Ok, how about

=INDEX(FILTERXML("<o><i>"&SUBSTITUTE(A2,"&amp;","</i><i>")&"</i></o>","//i"),2)

If the & can appear in multiple places, rather than just the two, then this may not work.

 

[Sharid]

Thanks fluff as always super work,

Is there any place I can literature on this so I know how to write it myself in future as I don't fully understand what you have done, but it works

 

[jtakw]

Hi,

A couple more formula options:

Book3.xlsx
	A	B	C
1	https://gp/help/seller/at-a-glance....eller=A23DQHPJ8M9RZ3&isAmazonFulfilled=10	seller=A23DQHPJ8M9RZ3	seller=A23DQHPJ8M9RZ3
Sheet742

Cell Formulas
Range	Formula
B1	B1	=LEFT(MID(A1,SEARCH(";seller=",A1)+1,255),FIND("&",MID(A1,SEARCH(";seller=",A1)+1,255))-1)
C1	C1	=TRIM(MID(SUBSTITUTE(A1,"&",REPT(" ",LEN(A1))),LEN(A1),LEN(A1)))

 

[Fluff]

Glad we could help & thanks for the feedback.

For more info on the function have a look here Excel formula: Text split to array | Exceljet