I'll assume you want to set the DV for cells A2:A100

Select A2:A100, with A2 as the active cell (this is important)

From the Excel main menu:
<data<validation
Allow: Custom
Formula:
=SUMPRODUCT(--ISERROR(SEARCH(MID(A2,ROW($A$1:INDEX($A:$A,LEN(A2) ,1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ ")))=0
Set the Error Alert and you're finished.

NOTICE: Cell A2 is the active cell in the selected range AND the formula
references cell A2. When you're done check the other cells...A4's DV formula
references A4, A10's references A10, etc.

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

XL2002, WinXP


"Kimberly" wrote:

Thank you - it does work! Now I'm just working on how to use it for an
entire column. Thank you!!

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