Excel conditional formatting rule for specific text string format

In Excel, what is the conditional formatting rule for changing a cell color if it contains either of the following strings:
"SUP /##"
"AIC /##"

Thanks,
Andrea
AndreamaryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Make a rule for each string for the cell value equal to the string.
Or a formula for both
=OR(A1="SUP /##",A1="AIC /##")
where A1 is the first cell in the range
0
AndreamaryAuthor Commented:
Hi Ejgil,

For some reason I can’t seem to get this to work. I tried it both ways, then realized I most likely, and I apologize, didn’t explain my question very well.

“SUP /##” whereby the ## is a wildcard for a numeric value, such as SUP /18, or SUP /29, etc.
And the same goes for “AIC /##”  (ie., AIC /55, AIC /10
Lastly, the number of digits after the “/“ could be one, two or three digits.

Hope this clarifies things... :-/

Andrea
0
Rob HensonFinance AnalystCommented:
Hi Andrea,

With Ejgil's suggestion, just replace the ## with *, ie:

=OR(A1="SUP /*",A1="AIC /*")

Will there always be a space between the 3 letters and the /?
Will there always be the /? If so, would "SUP*" or "AIC*" cover the options?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AndreamaryAuthor Commented:
Thanks, Rob. For some reason I am not having success in getting this formula to work, so thought it best to supply an example Excel file. The column I am trying to apply the conditional formatting to is column D:

CondFormatting.PNG
The answer is 'Yes' to both of your questions. That being said, this represents an 'incomplete' format, hence the desire for conditional formatting to identify it as such. Once all the columns are populated (as column D is a concatenated string), then the final, complete format will be as shown below, at which point the conditional formatting should no longer take effect:

SUP or AIC ##/## or SUP or AIC ##/###

For example:
SUP 17/18
or
SUP 17/181
or
AIC 17/18
or
AIC 17/181

I hope this helps...

Andrea
0
AndreamaryAuthor Commented:
Sorry, forgot to upload my sample Excel file!

Here it is...

Andrea
EE_ConditionalFormatting.xlsm
0
Rob HensonFinance AnalystCommented:
I will take a look later
0
Shaun VermaakTechnical Specialist/DeveloperCommented:
You can use
=OR(LEFT(D2,5)="SUP /",LEFT(D2,5)="AIC /")

Open in new window

cf.png
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Shaun's suggestion using LEFT works. Don't understand why using a wild card doesn't though.
0
Ejgil HedegaardCommented:
Since column D is a composed value, I would use the cells that compose the value, instead of the result.
=AND(OR(A2="AIC",A2="SUP"),OR(B2="",C2=""))

But what you really want to show are the missing values in B or C, so I would highlight them instead.
Like this for column B and C
=AND(OR($A2="AIC",$A2="SUP"),B2="")
0
AndreamaryAuthor Commented:
Thanks for everyone's input...much appreciated! Shaun's solution was the first one to work, and fulfilled my request, and I appreciated the insights and alternate solutions provided by Ejgil, so I have shared the points.

Cheers,
Andrea
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.