Data Validation for text only formula

[Guest]

I was given this formula and I am trying to enter it in a cell to only allow
the user to enter text howerver it does not fit. Any suggestions?
=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)

 

[Gord Dibben]

Kimberly

The formula does not go into a cell.....it goes into the Custom Formula dialog
box in Data>Validation>Custom

Just CTRL + C the formula from your post then CTRL + V into that dialog box.

NOTE: this formula is written to limit A1 only to text.

Adjust the cell ref if placing in other cells.


Gord Dibben MS Excel MVP

 

[Biff]

Here's a sample file with this implemented:

Data Validation.xls 14.0kb

http://cjoint.com/?ljukUJHGNl

Biff

 

[Guest]

Thank you both! Thanks Biff - I didn't know how else to respond to you.

 

[Biff]

You're welcome. I hope we have this straightened out!

Biff