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

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

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

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

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

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