Andreamary
asked on
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
"SUP /##"
"AIC /##"
Thanks,
Andrea
ASKER
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
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
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?
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?
ASKER
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:
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
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
ASKER
I will take a look later
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Shaun's suggestion using LEFT works. Don't understand why using a wild card doesn't though.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Cheers,
Andrea
Or a formula for both
=OR(A1="SUP /##",A1="AIC /##")
where A1 is the first cell in the range