
Active Member

March 23, 2019

I am looking for a way for only specific text to be entered into a cell - the cell to only accept CAPITAL letters and no leading or following spaces.
Let's say these cells can only have 2 characters / some I have will have 3 or 4 Words.
I need the cell to accept only GM, GA, KA, LA, PA not GA<SPACE>, GM<SPACE> , KA<SPACE>
(If a space is put behind the letters then when I am writing formulas with SumIF(s) then it will not pick up information from the cells with the space.
I have tried writing a formula to drop in Data Validation but it will not accept my formula =OR(exact(A1)="GM",exact(A1)="GA")
I am at my witts end 🙁

VIP

Trusted Members

December 7, 2016

Hello,
There is a blog article written by Mynda that should help you forward. Check this out.
https://www.myonlinetraininghu.....it-entries
Br,
Anders

VIP

Trusted Members

June 25, 2016


VIP

Trusted Members

December 7, 2016

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

VIP

Trusted Members

June 25, 2016


VIP

Trusted Members

December 7, 2016

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

VIP

Trusted Members

June 25, 2016



Trusted Members
Moderators

November 1, 2018


Active Member

March 23, 2019

SunnyKow said
Hi GaylaYou 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

VIP

Trusted Members

December 7, 2016

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
1 Guest(s)
