As far as I am aware, any Data Validation does not execute if values are
pasted in.

Your best (only?) solution is to use a worksheet event module (VBA) to check
your condition.

Right click on w/sheet tab and copy/paste code below:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = True
If Target.Address = "$A$2" Then
c = Target.Value
If Application.Or(InStr(1, c, " ") 0, InStr(1, c, Asc(160)) 0) Then
Target.Value = ""
MsgBox "Invalid entry"
End If
End If
ws_exit:
Application.EnableEvents = True


End Sub
"Bob" wrote:

Thanks for the suggestion. Unfortunately, your solution still allows someone
to paste (instead of manually inputting) some text with a space, and not get
trapped by the validation rule.
Bob


"Toppers" wrote:

try:

=AND(NOT(ISNUMBER(FIND(" ",A2))),NOT(ISNUMBER(FIND(CHAR(160),A2))))

HTH

"Bob" wrote:

I have the following custom validation rule that checks for, and disallows,
the inputting of a space or CHAR(160):

=AND(ISERROR(FIND(" ",A2))=TRUE,ISERROR(FIND(CHAR(160),A2))=TRUE)

However, if someone pastes some text containing a space, the validation rule
doesn't trap it!

Can anyone tell me why this occurs, and a possible fix to my validation rule?
Thanks,
Bob