CODEWITHSUNDEEP
excelexcel-formula
Joe
Joe

Reputation: 205

Extract URL Query Value in Excel

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:

Upvotes: 2

Views: 2924

Answers (1)

Jack Deeth
Jack Deeth

Reputation: 3357

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

Upvotes: 3

Related Questions