Restricting entry to a combination of 7 letters

[edhunter]

Hello All,

I want to restrict entry to cells to only 7 letters (combination thereof though each character can appear once). I have been using this formula successfully for 5 characters (=LEN(SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE(UPPER(R2),"R","",1)),"I","",1)),"E","",1)),"A","",1)),"F","",1))=0) though when i add another two characters (G and Y), the validation rules breaks down and doesn't validate anymore. I guess i am reaching the limits of the validation input formula.

So any better way to do this, with VBA or another magic formula which can do the job.

thanks,
Ed

[wfm007]

Pls attach the reference file

[edhunter]

Book3.xlsx an example file

[wfm007]

Kindly confirm you want a validation wherein if cell value is without (RIEAFGY) then only it allows me to enter the word

[edhunter]

Yes, in need of a validation rule where you can only enter any combination of the 7 letters (without replacement). Thus only allowing entry of combination of the 7 letter. e.g. r; eag; ygfaeir; are all valid entries wheres these are not: any letter not in the combination: rp; efgh....etc and if one of the letters is repeated in the entry e.g. rre is not valid since r appears twice.
hope this clarifies, thanks.

[wfm007]

There u go.....

[edhunter]

Hey, thanks for your input on this.

Though the problem is still that one is able to enter combination of permitted letters with non-permitted, e.g.: ro; rp..etc are being accepted by the rule. In addition the rule is also permitting the input of the same letter more than once.

[shg]

Perhaps instead,

=AND(ISNUMBER(FIND(MID(C7, ROW(INDIRECT("1:7")), 1), "AEFGIRY")), LEN(SUBSTITUTE(C7, MID(D11, ROW(INDIRECT("1:7")), 1), "")) >= LEN(C7) - 1)

[edhunter]

Hmm thanks shg, though it doesn't detect the fault entries with the correct entries. e.g. RIu is accepted by the formula....

[shg]

Typo:

=AND(ISNUMBER(FIND(MID(C7, ROW(INDIRECT("1:7")), 1), "AEFGIRY")),
LEN(SUBSTITUTE(C7, MID(C7, ROW(INDIRECT("1:7")), 1), "")) >= LEN(C7) - 1)

[edhunter]

still...Accepts any wrong entry if there is already a correct entry letter, such as: iu; where i s ok but not u. In addition, repeated entries are also permitted - which shouldn't be, e.g.: eafgg (though it captures single letter repeated entries: ggg) .

[JosephP]

there's gotta be a simpler solution but I reckon this works

=AND(LEN(C7)<8,MAX(FREQUENCY(MATCH(MID(C7,ROW(INDIRECT("1:"&LEN(C7))),1),$H$1:$N$1,0),MATCH(MID(C7,ROW(INDIRECT("1:"&LEN(C7))),1),$H$1:$N$1,0)))=1,COUNT(MATCH(MID(C7,ROW(INDIRECT("1:"&LEN(C7))),1),$H$1:$N$1,0))=LEN(C7))

[shg]

Are we looking at the same thing?

In the examples below, the validation formula is in the adjacent cell.

Please Login or Register  to view this content.

You are using this for data validation, correct? If it's entered in a cell, it MUST be confirmed with Ctrl+Shift+Enter.

[edhunter]

--A-- --B--
1 EAFGG FALSE > Correct: Since G is repeated twice
2 EE FALSE> Correct since EE is repeated twice
3 IU FALSE> Correct since U is not one of the 7 letters
4 EAFG TRUE> Correct: Since all are from the 7 letters and inputted once.

[shg]

I know they are correct; what I'm missing is why you say it doesn't work.

Also, the formula is case-sensitive, so all of the examples you gave in post #5 would fail.

[edhunter]

=AND(ISNUMBER(FIND(MID(C7, ROW(INDIRECT("1:7")), 1), "AEFGIRY")),LEN(SUBSTITUTE(C7, MID(C7, ROW(INDIRECT("1:7")), 1), "")) >= LEN(C7) - 1)

The above formula returns "AEE" as True, where it should be false since EE is entered twice.

[JosephP]

it returns false if you array-enter it as stated by shg

[edhunter]

How do you do that in a validation formula box?

[JosephP]

you don't have to in a dv box-it's automatic-and that formula works for me as a dv source

[edhunter]

Great!!! Many thanks guys really for the time, believe me this is for a better cause. I added upper(), so now it work for lower case as well.

=AND(ISNUMBER(FIND(MID(UPPER(P7), ROW(INDIRECT("1:7")), 1), "AEFGIRY")),LEN(SUBSTITUTE(UPPER(P7), MID(UPPER(P7), ROW(INDIRECT("1:7")), 1), "")) >= LEN(P7) - 1)

best,
ED

[shg]

Or just replace FIND with SEARCH.

[JosephP]

you'll still need upper for the substitute function to work though