Restricting entry to a combination of 7 letters

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

Pls attach the reference file

Book3.xlsx an example file

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

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.

There u go.....

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.

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

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) .

--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.

=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.

How do you do that in a validation formula box?

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