Data Validation

[Guest]

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)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

I can't seem to get that either. I'm still trying though. What do you think
about this code? Only prob is that I can't get it for the entire column.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo OuttaHere
If Target.Address = "$F$4" Then '<<<< change cell
Application.EnableEvents = False
Dim strText As String
Dim lngN As Long
Const str_Chars As String = "[0-9a-zA-Z ]"
strText = Target.Text

For lngN = 1 To Len(strText)
If Not Mid$(strText, lngN, 1) Like str_Chars Then
MsgBox "Only numbers or alphabetic characters allowed. ", _
vbOKOnly, "Data Validation"
Application.Undo
Exit For
End If
Next 'lngN
End If
OuttaHere:
Application.EnableEvents = True
End Sub