Excel Assignment

[pgjons]

Hello,

I have an excel assignment and i am wondering if someone could help me out.

Here is my task:

In Column A there are long URLs with keys and values.

For example: http://ad.doubleclick.net/adj/activecom.tan/running/content;tile=1;channel=running;subchannel=nosubchannel;tanproduct=activecom;b2borb2c=b2c;feature=content;subfeature1=channel_home;subfeature2=nosubfeature2;subfeature3=nosubfeature3;sz=728x90;position=leader_top;dcopt=ist;eventid=;eventlisting=;country=;state=;typeid=;gender=ng;age=;skill=;traveler=n;demo_a=;estate=;etype=;esubtype=;ezip=;assetid=4e2fe191-1d0d-4b90-9d6d-8dc9b1c03717;dest_dma=;view=running;ord=845674?

This URL has many keys and values one of which would be channel = running (see bold above)

My task is to create a formula that extracts certain keys and values in each URL. Please note that some of the keys and values will be different for other URLs.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Channel[/TD]
[TD]Subchannel[/TD]
[TD]Feature[/TD]
[TD]sz[/TD]
[/TR]
[TR]
[TD]http://ad.doubleclick.net....etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So looking at the above table, I would need to come up with a formula in B2 that will provide the Key(Channel) and the Value(running) for the corresponding URL.

Also, the best solution will allow me to change the key in B1(Channel) to any other key and it should dynamically populate the values in the below rows.

Your help is greatly appreciated!

Thank You

 

[West Man]

People generally don't provide complete solutions for homework assignments. However here is some direction. Mid will extract a portion of a string. It needs a start and a length. If you can FIND the start of the string and FIND the end you can get the length.

 

[pgjons]

Thanks for the help. I did have another question...what is the best route to dynamically populate values depending on which "key" or name is inserted?

 

[West Man]

Have the formula refer to a cell containing the key rather than hard coding the key