Need to parse data between two specific CHARS

I am trying to parse out the Keywords from a Search String:

http://www.google.com.hk/search?q=construction+equipment&hl=zh-CN&newwindow=1&safe=strict&biw=1280&bih=649&prmd=mc&ei=TrDvTKqkE5G2vQPT5YGdDg&start=20&sa=N

I would like to return the value: "construction equipment". I also need to be able to remove each "+" from the parsed string.

I have tried very hard, but I don't know enough formulas to get the job done. Does anyone have any ideas?

Originally Posted by Andy Pope

This assume the information before and after the required text is consitent.

=SUBSTITUTE(MID(A1,FIND("search?q=",A1)+9,FIND("&hl=",A1)-FIND("search?q=",A1)-9),"+"," ")

Thanks for the quick reply, Andy! Unfortunately, and I apologize for thinking to note this, the search strings do vary:

Examples:

...ww.google.com.hk/search?q=construction+equipment&hl=zh-CN&newwindow=1&safe=strict&biw=1280&bih=649&prmd=mc&ei=TrDvTKqkE5G2vQPT5YGdDg&start=20&sa=N

...ww.google.ca/search?hl=en&rls=com.microsoft%3Aen-us&q=cost+of+construction+equipment&aq=f&aqi=&aql=&oq=&gs_rfai=

...ww.bing.com/search?q=dismantel+construction+Equipment%2c+houston%2c+texas&go=&qs=n&sk=&first=31&FORM=PERE2

...ww.google.com/search?q=construction%2bEQUIPMENT+texas&hl=en&source=hp&aq=f&aqi=&aql=&oq=&gs_rfai=

So, I need a formula that:
1) parses all data between "q=" and the first "&".
2) Replaces each "+" with a space
3) Replaces each "%2"+1 with a space. (I say +1 because sometimes it will be %20, %2b, %27, %2F, etc.)

Am I trying to do the impossible here?

Originally Posted by Leith Ross

Hello tshrader,

Welcome to the Forum!

Are you looking for a worksheet formula or a VBA solution?

I'm looking for a worksheet formula, if possible. Any help you can give would be greatly appreciated. I've run out of ideas.

Thanks!

Originally Posted by Andy Pope

You will need to expand the SUBSTITUTE functions to handle more of the possible fill in text.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("q=",A1)+2,FIND("&",MID(A1,FIND("q=",A1)+2,LEN(A1)))-1),"+"," "),"%2b", " "),"%2c"," ")

That worked great! Thanks, Andy!

I do have one last modification to the formula if you're feeling up to it. Instead having so many SUBSTITUTE formulas, is there a to have one SUBSTITUTE formula that searches for each "%" and then adds the next two characters regardless of what the characters are? I ask because there are hundreds of possible "%2X" combinations. %20, %22, %2F, %2D, %2B, $27, etc...

I tried for over an hour to find something that will work, but all the formulas I keep using only pull numerical positions, not the actual string. Any ideas? If not, I really appreciate the help thus far. Thank you very much.

Originally Posted by Andy Pope

In order to substitute all those possible combinations you will need to use code.

this user defined function should work.

Please Login or Register  to view this content.

the cell formula would be,

=extractdata(A1,"q=","&")

Forgive me, but I can't seem to get it to work. I went into VBA, created a module named "extractdata", copied the code, saved it, and then went into my excel sheet, copied the formula, changed the cell reference number from A1 to E5 (that's where it starts), and then received the '#NAME?" error. What do you think I'm doing wrong?

Do you know a good book to read to learn Excel VBA?

Thanks, Andy! Again, you're quite a lifesaver.

Originally Posted by Andy Pope

See attached file.

See previous thread on book and resources
http://www.excelforum.com/excel-prog...-vba-with.html

Wonderful! Thank you very much, Andy. That was perfect.

Originally Posted by Marcol

You could avoid using VBa with this approach

If you need to keep the original URLs try this on a copy of your data

Find & Select > Replace.....
Find what:=

Please Login or Register  to view this content.

Replace with:= (enter a single space)

Replace all

Find what:=

Please Login or Register  to view this content.

Replace with:= (enter a single space)

Replace all

Then use Andys' formula

Please Login or Register  to view this content.

or to keep the result uniform

Please Login or Register  to view this content.

URL Encoding should always be three characters %??, hence the search string.

You could make use of this coding, but it would probably need VBa, by converting the URL Encoding to its' Ascii equivalent
e.g.

Please Login or Register  to view this content.

etc......

Hope this helps.

This works as well. Thank you very much for your help, Marcol. I really appreciate it.