• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation - Character Limit & Uppercase Only

J

JPhotonics

Member
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.
 
Why are you using ranges?
Try =AND(EXACT(A2,UPPER(A2)),LEN(A2)<=28)
Then copy - paste special - validation , applied to the range.
 

Attachments

  • validation.xlsx
    8.5 KB · Views: 6
JPhotonics
Your 1.1111E+130 isn't text and now it has 11 characters or how?
If You add ' in front of Your number, then it will work as You need.
 
Yeah, I'm an idiot...
The issue is that I had the cell FORMAT set to 'GENERAL'.
Once I changed it to 'TEXT', it worked fine.
It was taking any long # string and converting it into ########+eXX which would keep it under the character limit, but obviously still not correct.
 
You must log in or register to reply here.
Back
Top