How to test if a cell has a value that is correctly formatted

[STEMALO]

I need a way to test is cell S5 = 0 or does not fit the following format TERM-########-####.

if yes then i will apply conditionnal formatting so the cell is red.

In case you need to use another cell as interim formula then i would use AA.

Any help would be greatly appreciated

 

[texasalynn]

maybe this?

=AND(S5=0,MID(S5,FIND("-",SUBSTITUTE(S5,"-","",1))+1,1)="-",MID(S5,FIND("-",S5),1)="-")

 

[STEMALO]

Here is what i have in column S
S5 = TERM-20081104-0392
S6 = 24618286
S7 = 0
S8 = T-1126-151
S9 = term20081104-0392
S10 = term-20081104-0392

Knowing that i need the data to follow the format: TERM-#######-####

The results should be:
OK
NOK
MISSING
NOK
NOK
OK

Is this possible?

 

[texasalynn]

your original message said you wanted to apply conditional format, and with the conditions you provided. I gave you a formula to do that.

Your new request is entirely different. Conditional format can not put a message/value. So do you want a formula in a blank cell to do this?

 

[STEMALO]

Depending on how the result ends up, ie: OK, NOK, MISSING i will then apply the conditionnal formatting for the NOK and MISSING to appear in RED.

Sorry for the confusion

OK=TRUE
NOK=FALSE
MISSING=FALSE

 

[texasalynn]

try this

Excel Workbook
	S	T	U
5	TERM-20081104-0392	*	OK
6	24618286	*	NOK
7	0	*	MISSING
8	T-1126-151	*	NOK
9	term20081104-0392	*	NOK
10	term-20081104-0392	*	OK
Sheet1

copy the formula I have in U down

 

[STEMALO]

Thanks for the help...it works perfect...