I can't figure it out for an entire column....do you have any suggestions?

"Ron Coderre" wrote:

DARN! I didn't copy enough characters from the formula!

It should be:
=SUMPRODUCT(--ISERROR(SEARCH(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1) ,1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ ")))=0

(I had left off the "=0" part

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

This isn't thoroughly tested, but it seems to be working.....

Select cell A1

From the Excel main menu:
<data<validation
Allow: Custom
Formula:
=SUMPRODUCT(--ISERROR(SEARCH(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1) ,1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ ")))

Note: In case text wrap impacts the display, there is only ONE space in that
formula. It is located after the letter Z.

Set the Error Alert and I *think* that does it.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Kimberly" wrote:

Can anyone help me validate a column to allow only text and spaces. No
punctuation. I used this below and was able to get only text but it also
prevents spaces.

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