Data Validation - Character Limit & Uppercase Only

[JPhotonics]

I am trying to set the character limit of my cells to 28 and force the characters to be uppercase using Data Validation so the user receives an error if either condition is not met.
I have the uppercase portion working fine, but cannot seem to get the length to function correctly. It seems Excel just ignores the length portion of my formula.

I know this can be done with VBA/Macro, but want to avoid if possible as there is other important VBA/Macro code part of the document which is owned by someone else.


Current situation:

Data Validation
Allow: Custom
Ignore Blank: Yes
Formula: =AND(EXACT(A2:A35,UPPER(A2:A35)),LEN(A2:A35)<=28)

AND(EXACT(A2:A35,UPPER(A2:A35)) - this portion appears to be functioning properly

Why can't I get the length to work? I have tried 28, <28, >28, <=28 nothing matters/works.
I tried LEFT and I also tried CONCATENATE. No dice.

 

[DSCfromCFA]

G'day JP,

Without knowing the cause, the problem seems to be with the range. I used your formula applied to a single cell and it worked for both the Upper Case and the Length.

Try applying the data validation to A2, then copying it down, which worked for me

Cheers

shane

 

[Kenneth Hobson]

=AND(EXACT($A$2:$A$35,UPPER($A$2:$A$35)),LEN($A$2:$A$35)<=28)

worked for me...

 

[JPhotonics]

=AND(EXACT($A$2:$A$35,UPPER($A$2:$A$35)),LEN($A$2:$A$35)<=28)

worked for me...

Odd, it appears to work properly or the Uppercase option, but not limiting the number of characters in the cell to 28 or less. It is allowing any length.

 

[Kenneth Hobson]

I am not sure why you need the ranges. A single cell should work too. Maybe due to possible row insertions?

If you post a link to an example file at a site like dropbox, we could troubleshoot.

Put =len(a2) in a cell and modufy a2. I did and it always worked. I typed A 28 times, enter and it was fine Another A and it errored as expected.

 

[JPhotonics]

I am not sure why you need the ranges. A single cell should work too. Maybe due to possible row insertions?

If you post a link to an example file at a site like dropbox, we could troubleshoot.

Put =len(a2) in a cell and modufy a2. I did and it always worked. I typed A 28 times, enter and it was fine Another A and it errored as expected.

I will try and upload when I get home, but figured this would be simpler! Haha

I tried the single cell (A2) way also. Still did not limit the character length in the cell, only uppercase.

Used:
=AND(EXACT(A2,UPPER(A2)),LEN(A2)<=28)

 

[Kenneth Hobson]

Here is my simple example file. https://www.dropbox.com/s/8oixhcpzai3qyrf/UCASEandLEN.xlsx?dl=0

In A2, custom data validation formula is what you used in #6 .
=AND(EXACT(A2,UPPER(A2)),LEN(A2)<=28)

 

[JPhotonics]

Here is my simple example file. https://www.dropbox.com/s/8oixhcpzai3qyrf/UCASEandLEN.xlsx?dl=0

In A2, custom data validation formula is what you used in #6 .
=AND(EXACT(A2,UPPER(A2)),LEN(A2)<=28)

Holy crap do I feel like a dummy!
I tried it again and couldn't figure it out... I downloaded your file and had the same issue I was seeing. It worked for using alpha characters, but numbers would just turn into '111111+e10', etc.

The CELL FORMAT was General.
I changed the Cell Format to TEXT - voila!

It works with a single cell or range, I'll probably just set it once and use the range for simplicity. Thanks!

 

[JPhotonics]

Just to follow up, the code that worked was cell-specific, not for a range.

=AND(EXACT(A2,UPPER(A2)),LEN(A2)<=28)