data validation

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

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

in your data validation dialog


--
Regards
Frank Kabel
Frankfurt, Germany


KK wrote:
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

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(S UBSTITUTE(SUBSTITUTE(SUBSTITUTE(D4,"a",""),"b","") ,"c",""),"d",""),"e",""),"f",""),"g",""))=0

KK wrote:
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.

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"KK" skrev i en meddelelse
...
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

Addendum:

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

LeoH

"Leo Heuser" skrev i en meddelelse
...
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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Hi Frank

Array constants are not allowed in data validation.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Frank Kabel" skrev i en meddelelse
...
Hi
try using a formula like
=SUMPRODUCT(--(ISNUMBER(FIND({"a","e","g"},A1))))

in your data validation dialog


--
Regards
Frank Kabel
Frankfurt, Germany

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))+ISNU MBER(FIND
("e",A1))

-----Original Message-----
Hi Frank

Array constants are not allowed in data validation.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Frank Kabel" skrev i en
meddelelse
...
Hi
try using a formula like
=SUMPRODUCT(--(ISNUMBER(FIND({"a","e","g"},A1))))

in your data validation dialog


--
Regards
Frank Kabel
Frankfurt, Germany




.