Match words - Conditional Formatting

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?

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




"KH_GS" <[email protected]> wrote in
message news:[email protected]...
>
> 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?
>
>
> --
> KH_GS
> ------------------------------------------------------------------------
> KH_GS's Profile:
> http://www.excelforum.com/member.php...o&userid=32920
> View this thread: http://www.excelforum.com/showthread...hreadid=527415
>

On Tue, 28 Mar 2006 20:29:29 -0600, KH_GS
<[email protected]> wrote:

>
>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

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?

Originally Posted by Ron Rosenfeld

On Tue, 28 Mar 2006 20:29:29 -0600, KH_GS
<[email protected]> wrote:

>
>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

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

Originally Posted by 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




"KH_GS" <[email protected]> wrote in
message news:[email protected]...
>
> 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?
>
>
> --
> KH_GS
> ------------------------------------------------------------------------
> KH_GS's Profile:
> http://www.excelforum.com/member.php...o&userid=32920
> View this thread: http://www.excelforum.com/showthread...hreadid=527415
>

On Tue, 28 Mar 2006 21:02:15 -0600, KH_GS
<[email protected]> wrote:

>
>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

=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

On Wed, 29 Mar 2006 02:26:05 -0600, KH_GS
<[email protected]> wrote:

>
>=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