data validation

[KK]

Hello,

I'm trying to use data validation to restrict the text allowed in a cell.

I want to allow the cell to contain any combination of 7 specified
characters. If the permitted characters are for example - a,b,c,d,e,f,g then
'a' is allowed, 'gddf' is allowed, 'gga' is allowed etc etc, but 'abx' is
forbidden. I can't use a list of permitted characters in the data validation
spec. because the number of combinations is huge. I might be able to use a
formula involving 'SUBSTITUTE' but it's messy.

Any ideas please?

Thanks

KK

 

[Frank Kabel]

Hi
try using a formula like
=SUMPRODUCT(--(ISNUMBER(FIND({"a","e","g"},A1))))

in your data validation dialog

 

[Debra Dalgleish]

You could use Substitute, then check the length of the remaining string.
For example, with data validation in cell D4:


=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D4,"a",""),"b",""),"c",""),"d",""),"e",""),"f",""),"g",""))=0

 

[Leo Heuser]

Hello

In data validation for e.g. cell A1 try this formula:

=SUMPRODUCT(ISERROR(SEARCH(MID(A1,ROW(
INDIRECT("1:"&LEN(A1))),1),"abcdefg"))+0)=0

 

[Leo Heuser]

Addendum:

If the entry in A1 is case sensitive, use
FIND instead of SEARCH

LeoH

 

[Leo Heuser]

Hi Frank

Array constants are not allowed in data validation.

 

[Frank Kabel]

Hi Leo
thnaks for the info (should have tested this). for the OP
you may then use something like
=ISNUMBER(FIND("a",A1))ISNUMBER(FIND("b",A1))+ISNUMBER(FIND
("e",A1))