Find words within cell & put text and color in adjacent cell [Solved/Closed]

- - Latest reply: fazal1
Posts
1
Registration date
Saturday October 22, 2016
Last seen
October 22, 2016
- Oct 22, 2016 at 02:49 AM
Hi there,
i'm trying to search for certain word(s) (i.e. "reply??" or "remind") in a cell with a sentence in it, and place a specific text on a different cell depending on the word it finds. If J3 text is "remind him to call", i'd like A3 to show "remind" in blue. If J3 text is "left message, waiting for reply??", i'd like A3 to show "reply??" in red.

Currently this is the function i'm using and only works for one word.
=IF(ISERROR(SEARCH("*reply*",J3,1)),"","reply?")

Any thoughts and help to solve this will be greatly appreciated.


See more 

4 replies

Best answer
3
Thank you
Hi,

Thanks EST for asking this and VCOOLIO for answering it and making my day. I had to adjust it as I needed one with 3 conditions and I thought I should share it for people who needs a 3 condition one:

=IF(J3="","",IF(ISNUMBER(SEARCH("reply",J3,1)),"Reply",IF(ISNUMBER(SEARCH("remind",J3,1)),"Remind","")),IF(ISNUMBER(SEARCH("banana",J3,1)),"banana"))))

I couldn't copy and paste mine from work for safety reason, but that one should work.
Thanks again and good luck for the others...
Greg

Thank you, Greg 3

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2576 users this month

vcoolio
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
-
Hello my Kiwi friend,

Thanks for adding that.

The formula is, basically, a nested "IF" function so if anyone else picks up on it, they can add a few more statements to it (up to 64 I believe) although it could become quite cumbersome.

Cheerio,
vcoolio.
Good to know! Thanks :)
DrConny
Posts
1
Registration date
Wednesday May 4, 2016
Last seen
May 4, 2016
-
HI Greg (and EST).
I am afraid I am getting an error reply (too many arguments on this function)when I try to use the one with 3 conditions .
Do you have any idea what might be causing that?

Thanks in advance!
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
1
Thank you
Hello Est,

I think that now we are on the money:-

=IF(J3="","",IF(ISNUMBER(SEARCH("reply",J3,1)),"Reply",IF(ISNUMBER(SEARCH("remind",J3,1)),"Remind","")))

You can drag the formula down as far as you like. Format the font colour by using Conditional Formatting.

Have a look at the updated example work book here:-

https://www.dropbox.com/s/wfbrnz5jjpp3uha/EST.xlsx?dl=0

Cheerio,
vcoolio.
VCoolio,
Oh yeah! you knocked that ball Outta the Par!!!! Home Run baby! THANK YOU!!!!
vcoolio
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
> EST -
Hello EST,
Seems like there is an office party going on over your way! Have one for me!
Glad that I could help.

Cheers,
vcoolio.
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
0
Thank you
Hello EST,

If there are only two sentences as per your post, then the following formula may do the job:-

=IF(J3="Remind him to call.","Remind",IF(J3="Left message waiting for reply?","Reply?",""))

If you have a look at the example work book:-

https://www.dropbox.com/s/wfbrnz5jjpp3uha/EST.xlsx?dl=0

you'll see that I've used a Data Validation drop down in J3. This is just as a safeguard to ensure that spelling, punctuation, spaces etc. are exactly as per the formula because any differences between the two, however minor, will not allow the formula to work.

I've used Conditional Formatting to colour the font as per your post.

BTW, this could be another option:-

=IF(J3="","",IF(ISNUMBER(SEARCH("reply?",J3,1)),"reply?","Remind"))

I hope this helps.

Regards,
vcoolio.
hi there Vcoolio, i appreciate your response greatly. However, the sentences are not ever the same, the only two items (2 words) that will be the same and should trigger the function or condition are: reply or remind. Here are a couple examples of sentences down the J column (i.e.J3=resent email - not about any specific client, catch-up be current; or J3=remind to check: Sponsor footprint (Room $1,000 2/28) room,tea,coffee,water; or J3=3/16 - 3/19 meet with with client, sent options... reply ???.) Hopefully this gives you a clearer sense of what the content is like. So l'd like to have the function/condition look through the sentence and if it spots either one of these 2 words, then drop the word in the corresponding C column and colorize it accordingly, repy is red, remind is blue.
Again, many thanks for your time and suggestion. HOwever it doesn't properly answer the issue yet.
hi there VCoolio, i sincerely appreciate the answer but sadly does not address my issue as I would like it to. My J column will have all kinds of sentences and i would like my function to search the sentence for only two specific words (reply, remind). My desire is to have either of these two words be the triggering aspect of the function so that if it finds the word "reply" within the sentence then put "reply" in C3, and if it finds the word "remind" within the sentence, then put "remind" in C3 or the adjacent C cell. If it doesn't contain either of these words, leave C blank. Here are a couple of exmples of J3 through J5.
J3 "resent email - not about any specific client, catch-up be current" | C3 display blank
J4 "remind Haley to call Mouse TODAY re: LUNCHEON" | C4 should display "remind"
J5 "3/16 - 3/19 meet with client, options sent, reply ??? | C5 should display "reply?"

I hope this helps clarify what i'm trying to have the function do. Best, EST.
Posts
1
Registration date
Saturday October 22, 2016
Last seen
October 22, 2016
0
Thank you
hey guys , i am trying to copy some text and paste in to another column but i dont know how to use formula plz advise. thx

task 1,,,,this is what i have info in my cells in rows..

1..aqua di gio 3.4 fl.oz eau de toilett for men .
2..aqua di gio 1.7 fl.oz eau de perfume for men .
3..aqua di gio 1.0 fl.oz eau de cologne for men.

from above cells i would like to take sizes such as 3.4 fl.oz , 1.7 fl.oz,1.0 fl.oz and paste those sizes to next column ....( sheet contains about 15 diffrent sizes ( text) which i need to find and copy paste to another column )

task 2,,,,
1..aqua di gio 3.4 fl.oz eau de toilette for men .
2..aqua di gio 1.7 fl.oz eau de perfume for men .
3..aqua di gio 1.0 fl.oz eau de cologne for men.

from above cells i want to find and paste diffrent text in to another cell..
.....
....eau de toilette......if found then in next column = EDT
....eau de perfume.....if found then in next column = EDP
....eau de cologne.....if found then in next column = EDC

meaning ...if 'eau de perfume' then i want to paste EDP in next column ..and so on ..( sheet contains about 12 diffrent types(text) which i need to find from more then thousand products in cell and copy paste diffrent text for them in next column )....

please advise , thank you very very much.