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

STEMALO

Board Regular
Joined
Apr 15, 2008
Messages
93
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
maybe this?

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

STEMALO

Board Regular
Joined
Apr 15, 2008
Messages
93
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?
 
Upvote 0

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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?
 
Upvote 0

STEMALO

Board Regular
Joined
Apr 15, 2008
Messages
93
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
 
Last edited:
Upvote 0

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
try this

Excel Workbook
STU
5TERM-20081104-0392*OK
624618286*NOK
70*MISSING
8T-1126-151*NOK
9term20081104-0392*NOK
10term-20081104-0392*OK
Sheet1


copy the formula I have in U down
 
Upvote 0

Forum statistics

Threads
1,191,608
Messages
5,987,642
Members
440,104
Latest member
thigarette

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
Top