Previous Topic Next Topic

Find words within cell & put text and color in adjacent cell

Solved/Closed
EST - Feb 27, 2015 at 03:17 PM
fazal1 Posts 1 Registration date Saturday October 22, 2016 Status Member 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.


4 responses

Answer 1 / 4
Greg
Apr 6, 2015 at 12:43 AM
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
3
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Apr 6, 2015 at 01:04 AM
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.
0
Greg
Apr 6, 2015 at 10:00 PM
Good to know! Thanks :)
0
DrConny Posts 1 Registration date Wednesday May 4, 2016 Status Member Last seen May 4, 2016
May 4, 2016 at 05:55 AM
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!
0
Answer 2 / 4
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Mar 2, 2015 at 06:12 PM
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.
1
EST
Mar 4, 2015 at 10:14 AM
VCoolio,
Oh yeah! you knocked that ball Outta the Par!!!! Home Run baby! THANK YOU!!!!
0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262 > EST
Mar 4, 2015 at 06:03 PM
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.
0
Answer 3 / 4
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Feb 28, 2015 at 06:44 AM
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.
0
EST
Mar 2, 2015 at 12:08 PM
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.
0
EST
Mar 2, 2015 at 03:28 PM
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.
0
Answer 4 / 4
fazal1 Posts 1 Registration date Saturday October 22, 2016 Status Member Last seen October 22, 2016
Oct 22, 2016 at 02:49 AM
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.
0

Similar discussions

Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response
help me to add particular cells
mathewmunna -
mathew munna -

2 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
Counting cells containing only numbers
jutulu -
jutulu -

4 responses
If cell = date return yes or no.
JD -
ndwebb -

5 responses