Extract URL Query Value in Excel

user_5858125 0
Viewed 209

I'm looking to extract a query value q= from a URL using Excel. The main challenge I'm facing is that the query value isn't always in the same position. I can have multiple parameters before or after the query value. All I know is that I need to start from the &q= position, and end at the next & symbol, or the end of line if no other & follows.

For example: in the URL below, I'm looking to extract the query value of: This_Is_My_Query_value

http://www.example.com/somepage?param1=one&param2=two&param3=three`&q=This_Is_My_Query_value`&A=Z&B=y&C=ahUKEwjS09bhyPD1AhVihOAKHTQyBeEQwL

Query string splitter:

  • 'param1': one
  • 'param2': two
  • 'param3': three
  • 'q': This_Is_My_Query_value
  • 'A': Z
  • 'B': y
  • 'C': ahUKEwjS09bhyPD1AhVihOAKHTQyBeEQwL
excel-formula excel
1 Answers

You can do this in two stages:

A1      (Your URL)
        http://www.example.com/somepage?param1=one&param2=two&param3=three&q=This_Is_My_Query_value&A=Z&B=y&C=ahUKEwjS09bhyPD1AhVihOAKHTQyBeEQwL

B1      =MID(@A:A,FIND("q=",@A:A)+2,999)
        This_Is_My_Query_value&A=Z&B=y&C=ahUKEwjS09bhyPD1AhVihOAKHTQyBeEQwL

C1      =LEFT(@B:B,IFERROR(FIND("&",@B:B)-1,999))
        This_Is_My_Query_value

Or you can do it in a single formula:

=LEFT(MID(@A:A,FIND("q=",@A:A)+2,999),IFERROR(FIND("&",MID(@A:A,FIND("q=",@A:A)+2,999))-1,999))

Replace @A:A with the reference to the URL cell.

This won't work if there's another q= earlier in the URL - if that's a concern it'd be possible to look for a ?q= or &q= instead of for q=.

user_24498570
Related