Contains command in a Formula

J

JRP4P

Hello and thank you so much in advance for helping me - I am working o
a complex project that requires me to identify certain text within
string of variables and label the cell as "Home Stereo", Electronics
etc.

A2 is
http://w.126x.com/?kw=home electronics&src=excel&ref=help
A3 is
http://w.126x.com/?kw=home%stereo&src=excel&ref=help

In B2 I would like to enter a formula that says if in A2 the word
"home" and "electronics" appear then B2 would say "Home Electronics".
And I would like in the same formula to assign different labels so tha
I can also include if in A2 the words "home" and "stereo" appear the
B2 would say "Home Stereo"

I have a lot of different URLs in column A and about 10 differen
labels that I need to assign to the right values in A depending on th
words that appear in the actual URL.

Is this possible? Thank you for your time
 
K

kkknie

If the kw response string is always the first one and there are no &'
in the value for kw, this function will work:

=PROPER(SUBSTITUTE(LEFT(RIGHT(A2,LEN(A2)-22),FIND("&",RIGHT(A2,LEN(A2)-22))-1),"%20",
"))

Here's what it does:
- Strip the left most stuff including ?kw=
- Find the first & and strip everything to the right of it
- Replace the %20 with a single space
- Change to Proper Case

If not, I don't think it can be done in one formula (but I could b
wrong).
 
Top