Conditional formating

[TMac52]

I have a cell with a string of text. I need to select 3 words of that text and format it per my selection. Example: bolt, stud, 1/2", A193, B7, ATS.
Pick out bolt, stud, 1/2 & B7 and format these cells red letters & yellow fill.
The size & grade (B7) will change as needed per size & grade needed.

 

[JoeMo]

Conditional formatting cannot be used to change the font color of a portion of a string. You would need VBA to do that.

 

[TMac52]

Conditional formatting cannot be used to change the font color of a portion of a string. You would need VBA to do that.

Conditional formatting cannot be used to change the font color of a portion of a string. You would need VBA to do that.

I apologize; let me rephrase my question. I am trying to choose cells to format that have certain words in the cells to match my criteria. Each cell contains a text string with variations of size, grade & ASTM code like the string below.
Bolt, Stud, 1/2" X 3-1/2" B7 A193 With 2-2H Nuts
I would like a formula for conditional formatting to pick all cells that contain the words Bolt, Stud, 1/2 & B7. I can choose to format these cells with whatever; it doesn't matter at this point. Just need a formula to get to that point. I appreciate any help I can get. I have 15,500 lines of text to sort through for several variations of the example given above.

 

[arthurbr]

Like this ? How to apply conditional formatting search for multiple words in Excel?

 

[TMac52]

Like this ? How to apply conditional formatting search for multiple words in Excel?

I'll try it. Thanks!

 

[TMac52]

Like this ? How to apply conditional formatting search for multiple words in Excel?

The formula you presented provides answer for any word in range name. I need all words in name range returned as answer. Can you help?

 

[JoeMo]

The formula you presented provides answer for any word in range name. I need all words in name range returned as answer. Can you help?

Do the words have to be in the specific order you listed them? If not, see if this CF formula works for you. If you want this to be case sensitive replace SEARCH with FIND. Note I assumed your cells containing the strings begin in A2 and go down col A. Select all the relevant cells first then Conditional Formatting>New Rule>Use a formula

The formula is:
=AND(ISNUMBER(SEARCH("Bolt",A2)),ISNUMBER(SEARCH("stud",A2)),ISNUMBER(SEARCH("1/2",A2)),ISNUMBER(SEARCH("B7",A2)))

 

[TMac52]

=AND(ISNUMBER(SEARCH("Bolt",A2)),ISNUMBER(SEARCH("stud",A2)),ISNUMBER(SEARCH("1/2",A2)),ISNUMBER(SEARCH("B7",A2)))

Thanks JoeMo. I entered the formula you sent in my spreadsheet and it returned True everywhere all the conditions were met. However, when I entered the same formula in the CF New Rule it returned nothing. Really bumfuzzled now!

 

[JoeMo]

Thanks JoeMo. I entered the formula you sent in my spreadsheet and it returned True everywhere all the conditions were met. However, when I entered the same formula in the CF New Rule it returned nothing. Really bumfuzzled now!

Works fine for me.
Check to see if the formula was entered correctly.
Did you select all the cells you wish to CF prior to entering the formula?
If you aren't using col A, post the exact CF formula you entered.

 

[TMac52]

Works fine for me.
Check to see if the formula was entered correctly.
Did you select all the cells you wish to CF prior to entering the formula?
If you aren't using col A, post the exact CF formula you entered.

Sorry for the late reply. Got a lot going on in different directions. I'll try again and let you know. Thanks much for the help!