Conditional & Validation Formatting - ISIN - Contains

StefanG · Jul 18th 2006

Hello,
Conditional formatting:
If a cell contains a string, say "NCT" or "nct", conditional format shall work. The string may be with a date entered i.e. "05/05/2006 NCT"

Same said cell is currently set to only allow dates.
How can i achieve that
Data Validation:
Can data validation be set to only allow a date, and/or "date NCT" as above?

Thank you,
Stefan

Dave Hawley · Jul 19th 2006

Re: Conditional & Validation Formatting - ISIN - Contains

Say what???

StefanG · Jul 19th 2006

Re: Conditional & Validation Formatting - ISIN - Contains

Hi Dave,
Sorry for any confusion caused.
Maybe one prob at a time.

How can I use conditional formatting to detect if a cell contains a certain string? So if a cell content is "NCT","nct", or "05/05/2006 NCT", fire conditional formatting.

Stefan

pangolin · Jul 19th 2006

Re: Conditional & Validation Formatting - ISIN - Contains

use under "formula is" of CONDITIONAL FORMAT

=RIGHT(A6,3)="NCT"

this assumes that you have to evaluate last three char of each string for NCT or nct

Dave Hawley · Jul 19th 2006

Re: Conditional & Validation Formatting - ISIN - Contains

Add the Custom Function below

Code

Function IsADate(strDate As String) As Boolean
    IsADate = IsDate(strDate)
End Function

Then use;

=OR(UPPER(A1)="NCT",ISadate(TRIM(SUBSTITUTE(UPPER(A1),"NCT",""))))

As the Formula is: in Conditional Formatting

StefanG · Jul 19th 2006

Re: Conditional & Validation Formatting - ISIN - Contains

Thank you very much.
I will look at these suggestions.
Your help is much appreciated.
Stefan

Conditional & Validation Formatting - ISIN - Contains

Participate now!

Share