Data Validation
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)
|