Hello,

There is a blog article written by Mynda that should help you forward. Check this out.
https://www.myonlinetraininghub.com/excel-custom-data-validation-limit-entries

Br,
Anders

Thank you. But this did not help.

I cannot find anything that will give me what I need.... :(.

Hi Gayla

You mentioned:

Let's say these cells can only have 2 characters / some I have will have 3 or 4 Words.

Did you mean 3 or 4 characters or words?

Are the characters manually entered or selected from a drop-down list?

Sunny

Hello Gayla,

Well, it's true that the article did not give a specific answer on how to get what you want, but it surely gives some hints to get you further on.
Anyway, paste in this formula in the Data Validation custom criteria. Of course do needed changes.

=AND(ISTEXT(A1),EXACT(A1,UPPER(A1)),LEN(A1)=2)

What this formula does is the following:
1) ISTEXT(A1) --> Checks if the data in cell A1 is text, if so then it gives the value of TRUE, else FALSE.
2) EXACT(A1,UPPER(A1)) --> Checks if the text in cell A1 is in upper case or not, if uppercase then it gives the value of TRUE, else FALSE.
3) LEN(A1)=2 --> Checks if the number of characters is two, if so then it gives the value of TRUE, else FALSE.

The AND() function just combines so that all three criterias are TRUE. If anyone of the above checks gets a FALSE value then the AND() function will give a FALSE value and then the data criteria is breached and the user gets an error message.
But with this solution you can not have a cell containing several words. If you need that then remove the LEN() criteria.

Br,
Anders

Hi Anders

I think you will also need to check A1 for leading and trailing space.

So another criteria is LEN(TRIM(A1))=2

Sunny

Hello Sunny,

Unfortunately the TRIM() function in a data validation does not do the actual job on the cell content. If you type in a space as first or last character, that space character remains but the criteria of only two characters length has passed as true, as the TRIM() function did of course trim out the space in the criteria check.

So I left it out using only LEN(A1)=2, so if you then try to write <space>GA in cell A1 it will result in a FALSE result, so the user gets an error message.

I do think that a VBA solution would be the best one for this scenario, but I am not good at writing VBA.

Br,
Anders

Hi Anders

I was referring to this:

=AND(ISTEXT(A1),EXACT(A1,UPPER(A1)),LEN(A1)=2,LEN(TRIM(A1))=2)

or even this

=AND(ISTEXT(A1),EXACT(A1,UPPER(A1)),LEN(A1)=2,LEN(A1)=LEN(TRIM(A1)))

It would compare the length before and after trimming.

Sunny

Your data validation formula could be

=EXACT(B2,VLOOKUP(B2,E2:E6,1))

with the cell you are typing into being B2 and the permitted list of inputs in E2:E6.

See attached.

Phil

As a matter of interest, why are capital letters important? SUMIF(S) wouldn't care about case.

Velouria said
As a matter of interest, why are capital letters important? SUMIF(S) wouldn't care about case.  

I say all Caps because that is the way that Management wants it on the report.  For what reason????

SunnyKow said
Hi Gayla

You mentioned:

Let's say these cells can only have 2 characters / some I have will have 3 or 4 Words.

Did you mean 3 or 4 characters or words?

Are the characters manually entered or selected from a drop-down list?

Sunny  

These letters are manually typed in to cells.  The are group of letters such as SL  BD  LTR  STR EXE PRO SPEC

Philip Treacy said
Your data validation formula could be

=EXACT(B2,VLOOKUP(B2,E2:E6,1))

with the cell you are typing into being B2 and the permitted list of inputs in E2:E6.

See attached.

Phil  

Hello,

To me it seems that Phil’s suggestion is what would suite you the best.

Br,

Anders