data validation formula

[Guest]

I am not able to enter this formula into the data validation in order to
allow a user to only enter text. any suggstions?

=SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1)

 

[Biff]

What exactly do you mean?

The length of the formula (213 chars) is within the length limit (255
chars). It works just fine on my end.

Try this:

Enter the formula in a cell then copy it and paste it into the validation
refedit box.

Biff

 

[Gord Dibben]

What is preventing you from doing so?

CTRL + c to copy it

Select A1 and Data>Validation>Allow>Custom>Formula............CTRL + v to
paste.


Gord Dibben MS Excel MVP

 

[Robert McCurdy]

Select your cells:

=NOT(ISNUMBER(A2))

Where A2 is the Activecell.


Regards
Robert McCurdy

I am not able to enter this formula into the data validation in order to
allow a user to only enter text. any suggstions?

=SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1)

 

[Lori]

Biff's formula should work fine, but as shorter alternative you could
try:

=AND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)>="a")

which also allows accented characters or for pure text try:

=SUM(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),"")))=LEN(A1)

Make sure A1 is selected when you enter this in the condition
formatting dialog box.