data validation

K

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
 
F

Frank Kabel

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

in your data validation dialog
 
D

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
 
L

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
 
F

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top