+ Reply to Thread
Results 1 to 18 of 18

Searching for multiple text strings in conditional formatting

  1. #1
    Registered User
    Join Date
    08-01-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Searching for multiple text strings in conditional formatting

    I am trying to apply a conditional formatting to A1 if it contains a specific text (NOT it's cell value). Is there a way to type a list of the specific text to look for in another column and somehow include a search function in the conditional formula of A1? I have been stuck with this one for a while. I will also need to apply different conditional formats depending on the text contained, and for this I assume that I will need to create a different list for each format, but both the LOOKUP and FIND functions will work only if the cell VALUE of A1 matches the text found in the list, NOT if it only CONTAINS the text. If it is possible, I would appreciate any suggestions or pointers. Thanks

  2. #2
    Valued Forum Contributor mangesh.mehendale's Avatar
    Join Date
    06-04-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    510

    Re: Searching for multiple text strings in conditional formatting

    I think Data Validation will resolve this....

    Select Cell A1.. Then Click on Data.. then Data Validation.. In Allow Select List.. in Source select list in other column..
    Don`t care, take care...

    Regards,
    Mangesh

  3. #3
    Registered User
    Join Date
    08-01-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Re: Searching for multiple text strings in conditional formatting

    Data validation is for restricting values entered in a cell, not at all what I am trying to do here, but thanks anyway

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Searching for multiple text strings in conditional formatting

    Pl See fiile.
    Formula is
    =IFERROR(MIN(IF(ISNUMBER(FIND($G$1:$G$8,A1)),ROW($G$1:$G$8),"")),0)=1
    Value 1 For First cell. This to be changed for each condition.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-09-2015
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    Office2007
    Posts
    47

    Re: Searching for multiple text strings in conditional formatting

    Dear toubab,

    See the attached file. Thanks.

    Sakib
    Attached Files Attached Files

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Searching for multiple text strings in conditional formatting



    Sorry for the off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    Registered User
    Join Date
    08-01-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Re: Searching for multiple text strings in conditional formatting

    Thanks to everyone, but none of these are working for me. I think maybe I didn't make myself clear in the question, so here is a sample of what I am trying to do.

    Cond format multi text.xlsx

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Searching for multiple text strings in conditional formatting

    What should happen if a cell contains multiple key words?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #9
    Registered User
    Join Date
    08-01-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Re: Searching for multiple text strings in conditional formatting

    Very good question and if it is up to me, I would use a different conditional formatting, not matter which key words are present. Not sure if it would work though.

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Searching for multiple text strings in conditional formatting

    Pl See file.
    Same Formula is used for CF. If you have more words from the list first word in the string of A cell will be used for CF by the formula.
    =IFERROR(MIN(IF(ISNUMBER(FIND($G$1:$G$8,A1)),ROW($G$1:$G$8),"")),0)=1
    Value 1 For First cell. This to be changed for each condition.
    Attached Files Attached Files

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Searching for multiple text strings in conditional formatting

    In the sample file...

    A1 = I do not fear computers. I fear the lack of them.

    D1 = keyword = computer

    Is computers in cell A1 a match with the keyword computer in cell D1?

  12. #12
    Registered User
    Join Date
    08-01-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Re: Searching for multiple text strings in conditional formatting

    Thank you for the formula. I see that you changed the Value for each condition, but the formula doesn't work for the other cells. For instance, A1 and A2 were highlighted after the word "computer" was found in the list, but A4 didn't not get highlighted with the match on "technology", same for A6 or A7 with "science". It looks like once the first match is detected, all other CF formulas are ignored, unless I am missing something here.
    Many thanks anyway for your input on the first formula :-)

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Searching for multiple text strings in conditional formatting

    Changed Formula
    Please Login or Register  to view this content.
    Value 1 For First word in list (computer). This to be changed for each condition.
    If you have more than one word from the list, first word coming in the list from G column will be used for CF by the formula.

    IN A2
    To err is human, but to really foul things up requires a computer.
    human and computer are the words. Since computer is coming first in the list(G column) Cf is accordng to word computer.
    technology word i have corrected in list.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 08-03-2015 at 02:41 AM.

  14. #14
    Registered User
    Join Date
    08-01-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Re: Searching for multiple text strings in conditional formatting

    Thanks again, but after playing a bit with your formulas, I noticed that when I change the order of the words in the list in G:G, only the first word (G1) is matched to whatever is found in A:A, the next 4 words are ignored. Is there a way around this?

  15. #15
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Searching for multiple text strings in conditional formatting

    I changed the values in G column . Still it is is OK. I think you have not typed the words instead cut and paste is done. Delete the words and type other words and check.

  16. #16
    Registered User
    Join Date
    08-01-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Re: Searching for multiple text strings in conditional formatting

    I just opened your file, deleted the words in G:G, retyped new ones and it is the same, only the 1st word I type in G:G (G1) gets matched in A:A, the others are ignored (G2, G3 and G4). It seems like we are very close , but not there yet

  17. #17
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Searching for multiple text strings in conditional formatting

    For each cell from G1 to G8 different colors are chosen for CF.You can verify this putting only word from G1 to G8.

  18. #18
    Registered User
    Join Date
    08-01-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Re: Searching for multiple text strings in conditional formatting

    Bingo!!! All I had to do is actually edit one of the CF rules to confirm the cell shade, click OK and apply before all the highlights showed up Thank you very much for your help!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Searching multiple text strings/variables
    By steve61 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-16-2014, 07:58 AM
  2. [SOLVED] Searching for multiple text strings
    By kschrieb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-12-2014, 08:38 PM
  3. Replies: 5
    Last Post: 06-12-2014, 09:23 AM
  4. [SOLVED] Formula for searching multiple text strings
    By Excelcious in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2014, 06:29 PM
  5. Formula for conditional formatting based on any of multiple text strings?
    By klenatron in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-28-2013, 03:31 PM
  6. Help searching an array of text strings for common strings
    By ABComp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-23-2012, 11:19 PM
  7. searching for multiple text strings
    By eddie in forum Excel General
    Replies: 4
    Last Post: 04-10-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1