Log In
Register
Home
Active Member
March 23, 2019
Offline
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
Offline
Hello,
There is a blog article written by Mynda that should help you forward. Check this out.
https://www.myonlinetraininghu.....it-entries
Br,
Anders
Active Member
March 23, 2019
Offline
Thank you. But this did not help.
I cannot find anything that will give me what I need.... :(.
VIP
Trusted Members
June 25, 2016
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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
Offline
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
October 5, 2010
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
As a matter of interest, why are capital letters important? SUMIF(S) wouldn't care about case.
Active Member
March 23, 2019
Offline
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????
Active Member
March 23, 2019
Offline
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
Offline
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)
