Validation - Custom - Not Upper

[Salza]

Dear all...

In one cell, I have set this formula as a validation. (Allow-Custom,
Formula)

=NOT(EXACT(F29,UPPER(F29)))

With that formula, no one can type a name of a classroom using all capital
letters.
For example, 5 APPLE, 5 BANANA, 5 CARROT
The cell can only accept 5 Apple, 5 Banana, or 5 Carrot.
And I want it to be that way.....

But I want the cell to also accept - 5A, 5B or 5C.
How to modify the formula?

Please advise.... thanks

Regards,
Salza

 

[Stephen Bye]

If these are predefined names, then just use validation type = List, and
give them a drop-down list to choose from.

 

[Salza]

Hi Stephen,

The classroom names are lots of them .... they are not predefined names.
So how???

It can be 5 Grape or 5 G, or 5 Pineapple or 5P.
This two forms should be acceptable but not 5 GRAPE or 5 PINEAPPLE.

Please help.

Thanks.

 

[Debra Dalgleish]

If all codes will be two characters, you could use the following:

=OR(LEN(F29)=2,NOT(EXACT(F29,UPPER(F29))))

 

[Stephen Bye]

Then how about
=EXACT(F29,PROPER(F29))
which will insist on capital letters at the start (only) of each word.

 

[Salza]

Dear Stephen,

Thanks a lot. It works like the one I want.
I really appreciate your kind help. You are really an expert.

Warmest regards,
Salza