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
fazal1 Posts 1 Registration date Saturday October 22, 2016 Status Member Last seen October 22, 2016 - Oct 22, 2016 at 02:49 AM
Related:
- Find words within cell & put text and color in adjacent cell
- Conditional formatting if cell contains text - Excel Forum
- If cell contains date then return value ✓ - Office Software Forum
- Compare 2 text cells and show difference in the third cell ✓ - Excel Forum
- Insert the current date and time in cell a1 ✓ - Excel Forum
- An example of a cell is a blank cell ✓ - Programming Forum
4 responses
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
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
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Mar 2, 2015 at 06:12 PM
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.
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
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
>
EST
Mar 4, 2015 at 06:03 PM
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.
Seems like there is an office party going on over your way! Have one for me!
Glad that I could help.
Cheers,
vcoolio.
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Feb 28, 2015 at 06:44 AM
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.
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.
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.
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.
fazal1
Posts
1
Registration date
Saturday October 22, 2016
Status
Member
Last seen
October 22, 2016
Oct 22, 2016 at 02:49 AM
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.
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.
Apr 6, 2015 at 01:04 AM
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.
Apr 6, 2015 at 10:00 PM
May 4, 2016 at 05:55 AM
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!