-1

How can I extract a specific word or words from a URL to display in another column on Google Spreadsheets? The URL is https://seatgeek.com/bands/katy-perry?p=3 and I have to extract "katy perry" from this URL. I also have to create a second formula that will display the same URL with a date from another column on the spreadsheet.

Derek Theaker
  • 1
  • 2

3 Answers3

1

Look up regular expressions for VBA. This way you can perform pattern matching with a lot of flexibility.

Here:

http://www.macrostash.com/2011/10/08/simple-regular-expression-tutorial-for-excel-vba/

or better yet, here:

How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

Community
  • 1
  • 1
mrmick
  • 81
  • 6
1

How's this - change A3 as needed to match the Cell with the URL: =SUBSTITUTE(MID(A3,SEARCH(";",SUBSTITUTE(A3,"/",";",4))+1,FIND("?",SUBSTITUTE(A3,"/",";",4))-SEARCH(";",SUBSTITUTE(A3,"/",";",4))-1),"-"," ")

What this is doing is switching out the '/' right before 'katy-perry' with a unique (to that cell) mark, the semi-colon. Then, using MID(), extract the info between the substituted ';' and the '?'.

Edit: This should work with any name length (i.e. 'katy-perry','katyyyyyy-peeerrryyy'). Note that it assumes that you will ALWAYS have a URL with four '/' before the artist's name.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • @DerekTheaker - I'm not sure, I've never used Google Spreadsheets, but I would bet they have a similar function that should help. Edit: It looks like Jeeped (below) has a Google Spreadsheets compatible solution. – BruceWayne Jun 03 '15 at 21:24
0

The single sample URL you provided leaves one wondering if the configuration is going to be standard across many other URLs you may have listed. If this is typical of the way other URLs are constructed, you can identify the question mark and the last forward slash to parse out the katy-perry. Here is is in steps then altogether.

The following instructions assume that https://seatgeek.com/bands/katy-perry?p=3 is in A1.

  1. Append a question mark to the end just in case there isn't one in the URL and use the first question mark found to strip off anything right of that.
          =LEFT(A1, FIND("?", A1&"?")-1)
          
  2. Replace all forward slashes with 99 spaces.
          =SUBSTITUTE(LEFT(A1, FIND("?", A1&"?")-1), "/", REPT(" ", 99))
          
  3. Peel off the right-most 99 characters and trim off extra spaces.
          =TRIM(RIGHT(SUBSTITUTE(LEFT(A1, FIND("?", A1&"?")-1), "/", REPT(" ", 99)), 99))
          

The result should katy-perry. This formula is Google-Spreadsheet friendly.

      Parse text from Google-Sheet

  • Just curious, why did you chose to do 99 spaces? Just to make sure you know a set amount to look for? – BruceWayne Jun 08 '15 at 15:49
  • @user3578951 - a) You need more spaces than characters in anything that you want to parse off and *9* didn't seem enough for all situations b) if you are going to type 2 digits, go for the maximum that those two digits allow; *50* seems silly when you can use the same number of digits for *99*. –  Jun 10 '15 at 16:48