Conditional formatting based of text only found within parentheses

jbarrick007

New Member
Joined
Jul 9, 2020
Messages
36
Office Version
  1. 365
Platform
  1. Windows
I have a chart that displays various tasks but then also who is it assigned to. I then have a drop down box with a list of all those roles, and a conditional format rule create dot highlight all the cells based of matching text. This is working with a few exceptions. I'd be grateful for recommendations on improvements.

For example, C11 says
• Produce Approved for Permit (AFP) Drawings - (TR, FR, DD)
C14 says:
• Send investigation meeting assignments to CFR through MS Teams - (FCR, DD)
and C17 says:
• Send out the SOW meeting invitation - (FR)

My problem is if I make my condition format, based of matching text of FR, it will highlight all three cells. I wouldn't want C14 highlighted because you do not find FR withing the parentheses, which is the spot for the assigned roles. It will highlight because it is finding FR within the abbreviation CFR.

If I change it to look for (FR) then I only get C17 highlighted, but I would still want C11 highlighted.

The only way I can figure to do this is create several more helpers cells to text join so that C11 and all other cells would look like
• Produce Approved for Permit (AFP) Drawings - (TR) (FR) (DD)

This is an easy fix, but that it changes the one one tab looks/reads compared to all the other tabs and documents.
Is there anyway to get it to search only after the hyphen or within the parentheses?

One of you genius' out there may have a solution. I'm hoping conditional formatting will still work and not resort to adding VBA. Thanks so much.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
How about
+Fluff New.xlsm
C
11• Produce Approved for Permit (AFP) Drawings - (TR, FR, DD)
12• Send investigation meeting assignments to CFR through MS Teams - (FCR, DD)
13• Send out the SOW meeting invitation - (FR)
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C11:C13Expression=ISNUMBER(SEARCH("FR,",SUBSTITUTE(C11,")",",")))textNO
 
Upvote 0
How about
+Fluff New.xlsm
C
11• Produce Approved for Permit (AFP) Drawings - (TR, FR, DD)
12• Send investigation meeting assignments to CFR through MS Teams - (FCR, DD)
13• Send out the SOW meeting invitation - (FR)
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C11:C13Expression=ISNUMBER(SEARCH("FR,",SUBSTITUTE(C11,")",",")))textNO
I apologize if I'm not understanding, or didn't explain enough in the first post.

I have the conditional format based of cell B3 which contains a drop down of all the roles. so I could pick FR TR or DD in order to get it to highlight all those roles across this entire sheet. Im currently using the conditional format rule "format only cells that contain" - Specific text - containing - =$B$3
 
Upvote 0
In that case change the formula to
=ISNUMBER(SEARCH($B$3&",",SUBSTITUTE(C11,")",",")))
 
Upvote 0
In that case change the formula to
=ISNUMBER(SEARCH($B$3&",",SUBSTITUTE(C11,")",",")))
I swear I tried that on my own. But I must have done something wrong. I ended up with adapting your formula for my specific sheet =ISNUMBER(SEARCH($B$3&",",SUBSTITUTE(A1,")",","))) and it works like a dream.

Thank you so much.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
In that case change the formula to
=ISNUMBER(SEARCH($B$3&",",SUBSTITUTE(C11,")",",")))

Fluff, Im so sorry. this isnt working perfectly for my. In my example at the start I used a comma as my separator when in reality it was a backspace, my mistake. But I can change that within the formula no problem. I dont want want to send the whole file for you to see, but there is still a glitch.

If I use =ISNUMBER(SEARCH($B$3&",",SUBSTITUTE(A1,")",","))) ---- I get correctly highlighted everywhere FR is the only role listed. such as • Send out the SOW meeting invitation - (FR), but I dont get lines that may say • Produce Approved for Permit (AFP) Drawings - (TR/FR/DD)

If I change the formula to =ISNUMBER(SEARCH($B$3&"/",SUBSTITUTE(A1,")",","))) I get the opposite. highlighted will be • Produce Approved for Permit (AFP) Drawings - (TR/FR/DD) but • Send out the SOW meeting invitation - (FR) will not be.

I tried inserting an OR which I probably did wrong because nothing was highlighted. =ISNUMBER(SEARCH($B$3&(OR(",","/")),SUBSTITUTE(A1,")",",")))

Can you please help me combine both =ISNUMBER(SEARCH($B$3&"/",SUBSTITUTE(A1,")",","))) and =ISNUMBER(SEARCH($B$3&",",SUBSTITUTE(A1,")",","))) then I think it will be highlighting everything Im looking for.
 
Upvote 0
You need to change the final comma to a forward slash as well
=ISNUMBER(SEARCH($B$3&"/",SUBSTITUTE(A1,")","/")))
 
Upvote 0
You need to change the final comma to a forward slash as well
=ISNUMBER(SEARCH($B$3&"/",SUBSTITUTE(A1,")","/")))
Man it is always the simple glitches that get you. I wish we could thank you and the other guru's with more than just clicking the like button.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top