Extract A String in an URL

[CoopertheRescuedog]

Hey guy, im stuck on this scenarion.

I have logs of thousands of entries such as this

https://seatgeek.com/bands/boston-red-sox?p=1
https://seatgeek.com/teams/coldplay?p=2
https://seatgeek.com/bands/katy-perry?p=3
https://seatgeek.com/teams/new-york-rangers?p=4

I want to be able to return the band name in the next column.

I need something that says return string between ".com/" and "?" from a url

[Pete_UK]

If your data starts in A2 and continues down column A, put this formula in B2:

=MID(A2,SEARCH(".com/",A2)+5,FIND("?",A2)-SEARCH(".com/",A2)-5)

and copy down.

Hope this helps.

Pete

[CoopertheRescuedog]

Thanks for the reply Pete. The data is in C2 ( I edited the formula) but the value returns a ?Name error

=MID(C2,SEARCH(".com/",cs)+5,FIND("?",cs)-SEARCH(".com/",cs)-5)

Whats the +5 -5 all about?

Any other ideas?

[ChemistB]

That should be C2 not cs

=MID(C2,SEARCH(".com/",C2)+5,FIND("?",C2)-SEARCH(".com/",C2)-5)

[CAABYYC]

Hi, Nice to see a neighbor (from Burnaby ,BC) here.

other option is as follow
=MID(LEFT(A2,FIND("?",A2)-1),FIND("com/",A2)+4,9999)
=SUBSTITUTE(SUBSTITUTE(A2,LEFT(A2,FIND("com/",A2)+3),),MID(A2,FIND("?",A2),9999),)

Thanks for the reply Pete. The data is in C2 ( I edited the formula) but the value returns a ?Name error

=MID(C2,SEARCH(".com/",cs)+5,FIND("?",cs)-SEARCH(".com/",cs)-5)

Whats the +5 -5 all about?

Any other ideas?

[Pete_UK]

Whats the +5 -5 all about?

As ChemistB has said, it should be C2 in the formula, and not cs.

SEARCH will return the character number where the match is first found, but you want to extract the data starting at 5 characters beyond the beginning of ".com/", so that explains where the 5 comes from.

Hope this helps.

Pete

[CoopertheRescuedog]

Awesome guys thanks so much, I had to change the 5 to an 11 and a few of the cells i had to change to 13 and -13 but it works! thanks so much!

[CoopertheRescuedog]

Now I need https://seatgeek.com/bands/boston-red-sox?p=1 to show like https://seatgeek.com/bands/u2?p=16

[Pete_UK]

If the URL is in A2, and you have boston-red-sox in B2, you can do this in C2:

=SUBSTITUTE(A2,B2,"u2")&6

but it is hard to generalise with only one example of what you want to achieve.

Hope this helps.

Pete

[CAABYYC]

=SUBSTITUTE(A2,MID(LEFT(A2,FIND("?",A2)-1),FIND("com/",A2)+4,9999),"bands/u")&6
OR
=SUBSTITUTE(A2,SUBSTITUTE(SUBSTITUTE(A2,LEFT(A2,FIND("com/",A2)+3),),MID(A2,FIND("?",A2),9999),),"bands/u")&6

[CoopertheRescuedog]

Thanks guys im getting there

This is what I need actually:
https://seatgeek.com/bands/u2/2014/10/08 is actually the syntax I need.

Here is what I have so far

https://seatgeek.com/bands/10/21/12?p=1 (from petes formula =SUBSTITUTE(A2,B2,"u2")) I removed the &6

Caabyyc great formula as well.

Is this tricky or am I just a newb?

This is my data set:

https://seatgeek.com/bands/boston-red-sox?p=1
https://seatgeek.com/teams/coldplay?p=2
https://seatgeek.com/bands/katy-perry?p=3
https://seatgeek.com/teams/new-york-rangers?p=4
https://seatgeek.com/theater/motown-the-musical?p=5