Conditional Formatting with Too Many Conditions

[peakoverload]

I have a column (F) in which the rows could contain anything. I want to use conditional formatting to highlight the cells that DON'T contain certain specific text or that are blank. The problem is that their are around 40 specific texts I don't want it to highlight. The text I don't want to highlight are:

Part1, Part2 etc....Part10
Part 1, Part 2 etc.....Part 10
Disc1, Disc2 etc.....Disc10
Disc 1, Disc 2 etc....Disc 10

I've been using the formula =AND($F4<>"PART1",AND($F4<>"PART2",AND($F4<>"PART3",AND($F4<>"PART4",AND($F4<>"PART5",AND($F4<>"PART6",AND($F4<>"PART7",AND($F4<>"PART8",AND($F4<>"PART9",AND($F4<>"PART10",NOT(ISBLANK($F4))))))))))))

This works okay but not if there is a space between Part and the number and not at all for Disc but it won't let me add any further conditions (I'm using the Mac version of Excel if that makes a difference)

The obvious solution would be to use a wild card Part* and Disc* but when I try that it highlights everything except blanks. I'm assuming when using <> not equal it then takes the text literally i.e. it's looking to see Part* or Disc*

Is there a way around this?

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Also, please go back to your previous thread and address the issues in this post: https://www.excelforum.com/excel-gen...ml#post5974195

Thank you.

I'm using the Mac version of Excel if that makes a difference)

It might - this is why we need to know details of your Excel version and have asked you to put them into your forum profile.

[AliGW]

Thanks for updating your profile. Is 16.88 a 365 edition? If so, please also add that to your profile.

Then please provide a sample workbook that includes a full list of all the texts that you would not want highlighting. Make sure that it has a good amount of sample data for us to use for testing.

[AliGW]

CF rule for A4:

=AND(A4<>"",LEFT(A4,4)<>"PART",LEFT(A4,4)<>"Disc")

Applies to: =$A$4:$A$27

[peakoverload]

Okay, that is a LOT simpler than what I was trying! I don't use Excel very often and hadn't come across Left before. Just looked it up and can already see how this would make some of my other formulas much easier and more accurate. Everyday is a school day

Thank you for this, much appreciated!

[AliGW]

I have come up with a tweak just in case you have other words starting with 'part' or 'disc':

=AND(A4<>"",LEFT(A4,4)<>"PART",LEFT(A4,4)<>"Disc",NOT(ISNUMBER(--TRIM(MID(A4,5,2)))))

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[AliGW]

Actually, that's not working! LOL! I shall tweak it again.

[AliGW]

This works:

=AND(A4<>"",OR(LEFT(A4,4)<>"PART",LEFT(A4,4)<>"Disc"),NOT(ISNUMBER(--TRIM(MID(A4,5,2)))))

[peakoverload]

That's perfect. Thank you again. There is the possibility that some folders might start with those letters so I will use this formula just in case.

[AliGW]

There's foresight for you!!!

Happy to help - thanks for the rep.