CF rule for Cell Value Containing LV, AL, SL, etc

[squirrellydw]

I'm back with another question. Trying to clean up some CF I made years ago with something easier. I currently have a bunch of CF rules for LV, AL, SL, etc. Is there away to have them all in one CF rule to cover the entire spreadsheet? I would like it to strikeout the name in the cell whenever the cell has " LV", " AL", " or " SL" listed in it. I currently have a rule for each column and it works fine but it's a lot of rules. So for example if the cell has Danny AL listed it would strike it out with a line through it. The AL has a space in front so if a name has AL in it, it would ignore it. It would need to cover B4 to Q34 if that helps.

Hope you guys can help me out one more time

 

[Peter_SSs]

Try this CF formula

=FIND(RIGHT(B4,3)," LV AL SL")

 

[squirrellydw]

Try this CF formula

=FIND(RIGHT(B4,3)," LV AL SL")

that doesn't seem to work, it filled in all the blank cells

 

[Peter_SSs]

it filled in all the blank cells

Hmm, I thought it was supposed to Strikethrough, not fill.

I would like it to strikeout the name in the cell whenever the cell has " LV", " AL", " or " SL" listed in it.

Try this instead

=AND(LEN(B4)>2,FIND(RIGHT(B4,3)," LV AL SL"))

 

[squirrellydw]

Try this CF formula

=FIND(RIGHT(B4,3)," LV AL SL")

Got it working, it was my mistake. What does the 3 in the formula do? Thank you again

 

[Peter_SSs]

Got it working,

Good news!

What does the 3 in the formula do?

RIGHT(B4,3) takes the last 3 characters from the cell. In your cells-of-interest that would be " AL" or " LV" etc

Was I correct in thinking that those texts you are looking for would always be at the end of the text?
.. or might a cell contain something like "Danny AL was early"

 

[squirrellydw]

Good news!


RIGHT(B4,3) takes the last 3 characters from the cell. In your cells-of-interest that would be " AL" or " LV" etc

Was I correct in thinking that those texts you are looking for would always be at the end of the text?
.. or might a cell contain something like "Danny AL was early"

ok got it. your thinking was correct. Thank you again for all the help

 

[Peter_SSs]

Thanks for the confirmation.