Condtional formating -- any wildcard use?

[MatthewTap]

I am new to the MS forums; I apologize if I am breaking etiquette of any
sort, but I'm looking for some help.

I'm wondering if there's any way when using Conditional Formatting to have
it specify if the cell contents are 'like' something.

For example, if I have entries in a column such as:
Red Car
Blue Car
Yellow Truck
Blue SUV
Yellow Car (convertible)
Black Pick-up


etc... and I want to make anything that's a car in that list formatted so
it's Bold.


And again, this is just an example, but if there's anything else I can help
clear up about it... I'd really like to know if this is possible.

Thank you very much for your time.
Best regards,
Matt

[Dave Peterson]

If you only care if there's the characters "CAR" in the cell, you can use:

=countif(a1,"*car*")>0
or
=countif(a1,"*"car*")
(0 is treated as false)
or

=ISNUMBER(SEARCH("car",A1))
or even
=SEARCH("car",A1)
(an error will be treated as false)

Be aware that "Dodge Caravan" will look like a car in all of these.


MatthewTap wrote:
>
> I am new to the MS forums; I apologize if I am breaking etiquette of any
> sort, but I'm looking for some help.
>
> I'm wondering if there's any way when using Conditional Formatting to have
> it specify if the cell contents are 'like' something.
>
> For example, if I have entries in a column such as:
> Red Car
> Blue Car
> Yellow Truck
> Blue SUV
> Yellow Car (convertible)
> Black Pick-up
>
> etc... and I want to make anything that's a car in that list formatted so
> it's Bold.
>
> And again, this is just an example, but if there's anything else I can help
> clear up about it... I'd really like to know if this is possible.
>
> Thank you very much for your time.
> Best regards,
> Matt

--

Dave Peterson

[MatthewTap]

Dave --

Thanks... I went with the first one, changing the conditional format to
Formula, and one thing I didn't know but figured out by your posts is that I
could put in A1 for the formula template and it took care of the rest of them.

In any case, thanks... my problem solved. I appreciate your time.

"Dave Peterson" wrote:

> If you only care if there's the characters "CAR" in the cell, you can use:
>
> =countif(a1,"*car*")>0
> or
> =countif(a1,"*"car*")
> (0 is treated as false)
> or
>
> =ISNUMBER(SEARCH("car",A1))
> or even
> =SEARCH("car",A1)
> (an error will be treated as false)
>
> Be aware that "Dodge Caravan" will look like a car in all of these.
>
>
> MatthewTap wrote:
> >
> > I am new to the MS forums; I apologize if I am breaking etiquette of any
> > sort, but I'm looking for some help.
> >
> > I'm wondering if there's any way when using Conditional Formatting to have
> > it specify if the cell contents are 'like' something.
> >
> > For example, if I have entries in a column such as:
> > Red Car
> > Blue Car
> > Yellow Truck
> > Blue SUV
> > Yellow Car (convertible)
> > Black Pick-up
> >
> > etc... and I want to make anything that's a car in that list formatted so
> > it's Bold.
> >
> > And again, this is just an example, but if there's anything else I can help
> > clear up about it... I'd really like to know if this is possible.
> >
> > Thank you very much for your time.
> > Best regards,
> > Matt
>
> --
>
> Dave Peterson
>

[Dave Peterson]

I should have said with A1 the activecell.

But glad you figured it out.

MatthewTap wrote:
>
> Dave --
>
> Thanks... I went with the first one, changing the conditional format to
> Formula, and one thing I didn't know but figured out by your posts is that I
> could put in A1 for the formula template and it took care of the rest of them.
>
> In any case, thanks... my problem solved. I appreciate your time.
>
> "Dave Peterson" wrote:
>
> > If you only care if there's the characters "CAR" in the cell, you can use:
> >
> > =countif(a1,"*car*")>0
> > or
> > =countif(a1,"*"car*")
> > (0 is treated as false)
> > or
> >
> > =ISNUMBER(SEARCH("car",A1))
> > or even
> > =SEARCH("car",A1)
> > (an error will be treated as false)
> >
> > Be aware that "Dodge Caravan" will look like a car in all of these.
> >
> >
> > MatthewTap wrote:
> > >
> > > I am new to the MS forums; I apologize if I am breaking etiquette of any
> > > sort, but I'm looking for some help.
> > >
> > > I'm wondering if there's any way when using Conditional Formatting to have
> > > it specify if the cell contents are 'like' something.
> > >
> > > For example, if I have entries in a column such as:
> > > Red Car
> > > Blue Car
> > > Yellow Truck
> > > Blue SUV
> > > Yellow Car (convertible)
> > > Black Pick-up
> > >
> > > etc... and I want to make anything that's a car in that list formatted so
> > > it's Bold.
> > >
> > > And again, this is just an example, but if there's anything else I can help
> > > clear up about it... I'd really like to know if this is possible.
> > >
> > > Thank you very much for your time.
> > > Best regards,
> > > Matt
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[David McRitchie]

Hi Matt,

Some additional comments -- finding "car" as a word as opposed to
being found within another word such as "Dodge Caravan" as Dave mentioned --

The following formula will allow you to color the entire row based on finding the
word "car" anywhere in the cell of column A on each row.
=SEARCH("* anymatch *"," " & $A1 & " ")
If you had commas in cells you could include SUBSTITUTE as well in formula.

A1 should be the active cell -- in this case since $A1 limits to column A the
active cell need only be anywhere on row 1.

Cells eligible for coloring by C.F. is limited to the selection when entering Conditional Formatting...

SEARCH is not case sensitive but does not return a zero if string is not found.
Getting "#VALUE!" will be treated as False (not True) in Conditional Formatting
so it is not necessary to code as
=ISNUMBER(SEARCH("* anymatch *"," " &$A1 & " ")
which would be neater if looking for the words True or False on the worksheet.

More information on Conditional Formatting in
http://www.mvps.org/dmcritchie/excel/condfmt.htm
More information on Strings (and specifically SEARCH with wildcard characters)
http://www.mvps.org/dmcritchie/excel....htm#wildcards

You will find the use of A1 or other cell as the active cell in a conditional formatting
formula similar to usage in Filtering -- so you are pretty much set on how filtering works.
http://www.contextures.com/tiptech.html

and of course use of the fill handle in filling formulas down (or across) in a spreadsheet
http://www.mvps.org/dmcritchie/excel/fillhand.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"MatthewTap" <[email protected]> wrote ...
> Dave (Peterson) --
>
> Thanks... I went with the first one, changing the conditional format to
> Formula, and one thing I didn't know but figured out by your posts is that I
> could put in A1 for the formula template and it took care of the rest of them.
>
> In any case, thanks... my problem solved. I appreciate your time.
>
> "Dave Peterson" wrote:
>
> > If you only care if there's the characters "CAR" in the cell, you can use:
> >
> > =countif(a1,"*car*")>0
> > or
> > =countif(a1,"*"car*")
> > (0 is treated as false)
> > or
> >
> > =ISNUMBER(SEARCH("car",A1))
> > or even
> > =SEARCH("car",A1)
> > (an error will be treated as false)
> >
> > Be aware that "Dodge Caravan" will look like a car in all of these.
> >
> >
> > MatthewTap wrote:
> > >
> > > I am new to the MS forums; I apologize if I am breaking etiquette of any
> > > sort, but I'm looking for some help.
> > >
> > > I'm wondering if there's any way when using Conditional Formatting to have
> > > it specify if the cell contents are 'like' something.
> > >
> > > For example, if I have entries in a column such as:
> > > Red Car
> > > Blue Car
> > > Yellow Truck
> > > Blue SUV
> > > Yellow Car (convertible)
> > > Black Pick-up
> > >
> > > etc... and I want to make anything that's a car in that list formatted so
> > > it's Bold.
> > >
> > > And again, this is just an example, but if there's anything else I can help
> > > clear up about it... I'd really like to know if this is possible.
> > >
> > > Thank you very much for your time.
> > > Best regards,
> > > Matt
> >
> > --
> >
> > Dave Peterson
> >

[MatthewTap]

Much obliged, thanks.

"David McRitchie" wrote:

> Hi Matt,
>
> Some additional comments -- finding "car" as a word as opposed to
> being found within another word such as "Dodge Caravan" as Dave mentioned --
>
> The following formula will allow you to color the entire row based on finding the
> word "car" anywhere in the cell of column A on each row.
> =SEARCH("* anymatch *"," " & $A1 & " ")
> If you had commas in cells you could include SUBSTITUTE as well in formula.
>
> A1 should be the active cell -- in this case since $A1 limits to column A the
> active cell need only be anywhere on row 1.
>
> Cells eligible for coloring by C.F. is limited to the selection when entering Conditional Formatting...
>
> SEARCH is not case sensitive but does not return a zero if string is not found.
> Getting "#VALUE!" will be treated as False (not True) in Conditional Formatting
> so it is not necessary to code as
> =ISNUMBER(SEARCH("* anymatch *"," " &$A1 & " ")
> which would be neater if looking for the words True or False on the worksheet.
>
> More information on Conditional Formatting in
> http://www.mvps.org/dmcritchie/excel/condfmt.htm
> More information on Strings (and specifically SEARCH with wildcard characters)
> http://www.mvps.org/dmcritchie/excel....htm#wildcards
>
> You will find the use of A1 or other cell as the active cell in a conditional formatting
> formula similar to usage in Filtering -- so you are pretty much set on how filtering works.
> http://www.contextures.com/tiptech.html
>
> and of course use of the fill handle in filling formulas down (or across) in a spreadsheet
> http://www.mvps.org/dmcritchie/excel/fillhand.htm
>
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "MatthewTap" <[email protected]> wrote ...
> > Dave (Peterson) --
> >
> > Thanks... I went with the first one, changing the conditional format to
> > Formula, and one thing I didn't know but figured out by your posts is that I
> > could put in A1 for the formula template and it took care of the rest of them.
> >
> > In any case, thanks... my problem solved. I appreciate your time.
> >
> > "Dave Peterson" wrote:
> >
> > > If you only care if there's the characters "CAR" in the cell, you can use:
> > >
> > > =countif(a1,"*car*")>0
> > > or
> > > =countif(a1,"*"car*")
> > > (0 is treated as false)
> > > or
> > >
> > > =ISNUMBER(SEARCH("car",A1))
> > > or even
> > > =SEARCH("car",A1)
> > > (an error will be treated as false)
> > >
> > > Be aware that "Dodge Caravan" will look like a car in all of these.
> > >
> > >
> > > MatthewTap wrote:
> > > >
> > > > I am new to the MS forums; I apologize if I am breaking etiquette of any
> > > > sort, but I'm looking for some help.
> > > >
> > > > I'm wondering if there's any way when using Conditional Formatting to have
> > > > it specify if the cell contents are 'like' something.
> > > >
> > > > For example, if I have entries in a column such as:
> > > > Red Car
> > > > Blue Car
> > > > Yellow Truck
> > > > Blue SUV
> > > > Yellow Car (convertible)
> > > > Black Pick-up
> > > >
> > > > etc... and I want to make anything that's a car in that list formatted so
> > > > it's Bold.
> > > >
> > > > And again, this is just an example, but if there's anything else I can help
> > > > clear up about it... I'd really like to know if this is possible.
> > > >
> > > > Thank you very much for your time.
> > > > Best regards,
> > > > Matt
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
>
>