Data Validation

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)

On Feb 7, 12:46 pm, Kimberly
wrote:
Can anyone help me validate a column to allow only text and spaces. No
punctuation.

Here is a macro that will do the job. Just copy and paste the
following into the worksheet code module that you are working with.
Hope that does it for ya....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyString As String
Dim ChrCnt As Integer
Dim MyColumn As Integer
'Set your column number in the next line by changing the
'number 2 after the = sign to the column you want...
MyColumn = 2
'Check to see if this is even the cell we want
If Target.Column = MyColumn Then
'If the cell is blank then allow it and skip the rest
If Target = "" Then
Exit Sub
End If

ChrCnt = Len(Target)
Do Until ChrCnt = 0
MyString = Mid(Target, ChrCnt, 1)
If Asc(MyString) = 65 And Asc(MyString) = 90 Or _
Asc(MyString) = 97 And Asc(MyString) = 122 Or _
MyString = " " Then
Else
'You may change the message displayed by editing the _
'text found in quotes on the next line of code.
MsgBox "Illegal entry Attempted", vbExclamation, _
"Text Only Allowed"
Target = ""
Exit Sub
End If
ChrCnt = ChrCnt - 1
Loop
End If
End Sub

Hi

If you just add the extra case for Space - Code(32) - then this seems to
work

=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))))+SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("32:32")),0))))=LEN(A1)

--
Regards

Roger Govier


"Kimberly" wrote in message
...
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)