Need Help with 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/active...ture2=nosubfea ture2;subfeature3=nosubfeature3;sz=728x90;position=leader_top;dcopt=ist;eventid=;eventlisting=;country=;state=;typeid=;g ender=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.

	Channel	Subchannel	subfeature1
http://ad.doubleclick.net....etc

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

[JBeaucaire]

If you match the entries in B1, C1, D1 to exactly what you're searching for, these formulas work best. So B1 would be "channel" not "Channel", and so on.

B1: channel
C1: subchannel
D1: subfeature1
A2: the text link (url)
B2: =LEFT(MID($A2, FIND(";" & B$1 & "=", $A2) + LEN(";" & B$1 & "="), 100), FIND(";", MID($A2, FIND(";" & B$1 & "=", $A2) + LEN(";" & B$1 & "="), 100))-1)

Copy B2 across and down as needed.

[pgjons]

Thanks so much for your help! I hate to ask this but do you think you could help explain how the formula translates to the process? In other words, how does each part of the function translate to the result?

I'll understand if that is too time consuming, just let me know.

Thanks again!

[JBeaucaire]

Read up on the following functions by pressing F1 and opening Excel's help file for them.

MID()
FIND()



IN the formula, we us the FIND() to find the string in row 1 (like "channel" for instance) in the original string. To ensure we find the correct part of the string we add the ";" in front of it and the "=" after it.

Once we find the position of that substring, we move forward to find the string that starts just after the "=". Now we have the starting point of the string we want.

Next, we do that same thing again, but now we're looking for first ";" that comes after the position we've already found. This tells us how many characters we want.

Then we feed those two numbers in the MID() function. MID() is used to display a part of a longer string by indicating where to start, then how many characters to display, both of those things we solve above.

You can use the Formulas > Evaluate Formula to watch that formula unfold one step at a time.

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.