Match words - Conditional Formatting

[KH_GS]

I want to fill a cell with color if it contains a word that contai
letters that I specify. For example words then end with letter "s".

Wildcard search doesn't seem to work?

What is the formula to use

 

[Peo Sjoblom]

use conditional formatting and formula is

=RIGHT(A1)="s"

click the format button and pattern and select the colour

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Ron Rosenfeld]

I want to fill a cell with color if it contains a word that contain
letters that I specify. For example words then end with letter "s".

Wildcard search doesn't seem to work?

What is the formula to use?

If the cell only contains one word, then:

=RIGHT(cell_ref,1)="s"


--ron

 

[KH_GS]

hmm what if it is a phrase and I want to check every word in the
phrase?

Is is necessary to manually delimit them by space first then run the
conditional format on each column?

 

[Ron Rosenfeld]

hmm what if it is a phrase and I want to check every word in the
phrase?

See Below

Is is necessary to manually delimit them by space first then run the
conditional format on each column?
No

=OR(ISNUMBER(FIND("s ",cell_ref)),RIGHT(cell_ref,1)="s")


--ron

 

[KH_GS]

How to set the argument for the "=right" so that it is within the cell
of my selection?

 

[KH_GS]

=OR(ISNUMBER(FIND("d ",$C:$C)), RIGHT($C:$C,1)="d")


doesn't seem to work?


Column C
apple
fruit
tree
apple
fruit
tree
forest
road
road
road
road
test
test
tree
car
car
tree
test
car
appl

 

[Ron Rosenfeld]

=OR(ISNUMBER(FIND("d ",$C:$C)), RIGHT($C:$C,1)="d")


doesn't seem to work?


Column C
apple
fruit
tree
apple
fruit
tree
forest
road
road
road
road
test
test
tree
car
car
tree
test
car
apple

It does not work because you entered cell_ref incorrectly. Cell_ref needs to
refer to a single cell.

You can enter it by, for example, selecting C1:C100 but, when you enter the
formula, enter

=OR(ISNUMBER(FIND("d ",C1)),RIGHT(C1,1)="d")

Excel will adjust the reference to refer to each cell.

OR, you can enter in just a single cell, with the appropriate reference to that
cell, and then use the format painter.



--ron