Conditional Format

[rbobcat1]

is there a way to use Conditional Format to tell you you are missing a specific amount of Characters?

Example:
You have a cell the will contain Numeric.Alpha (0123456789.A).

does not matter what the value is except (10 numbers.1 Character) only, no more no less.

Ths

 

[Debraj]

Hi rbobcat1

I guess you are searching for Data Validation not Conditional Format..
Please confirm if not..

If your data is in a2.. then try this in Data > Data Validation > Custom > Formula as..

=(MID($A2,11,1)=".")*(LEN($A2)=12)*(AND(ISNUMBER(MID($A2,ROW($1:$10),1)+0)))*(ISTEXT(RIGHT($A2)))

 

[Somendra Misra]

Hi rbobcat1,

I hope you will always check for this standard format <10 Numeric>.<1 Alphabet>, if so than see the file.

Regards,

 

[rbobcat1]

wow your right data validation is part of what i wanted..

i want it to check to make sure it follows that validation and if it doesnt give me the conditional format.

thx guys..

 

[rbobcat1]

Hi rbobcat1

I guess you are searching for Data Validation not Conditional Format..
Please confirm if not..

If your data is in a2.. then try this in Data > Data Validation > Custom > Formula as..

=(MID($A2,11,1)=".")*(LEN($A2)=12)*(AND(ISNUMBER(MID($A2,ROW($1:$10),1)+0)))*(ISTEXT(RIGHT($A2)))

yes it would really be data validation but with conditional format if it does not meet the 10.1 format.

 

[NARAYANK991]

Hi Deb ,

Please check the formula against an input like : 1111111111..

ISTEXT does not confirm that a character is a letter of the alphabet.

Narayan