Extract URL Query Value in Excel-CodePudding

Im looking to extract a query value q= from a URL using excel. The main challenge im facing is that the query value isn't always in the same position. I can have multiple parameters before or after the qeury 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, im 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

CodePudding user response：

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=.