The only problem with Data Validation is that you get an error message and
you have to re-type.
You could set up event code to truncate anything over a certain number of
characters after user hits ENTER key
No message, no retyping.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'edit to suit
' "A1,A2,B1,C5,C6" for a non-contiguous range example
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) > 20 Then
.Value = Left(.Value, 20)
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
This is sheet event code. Right-click on the sheet tab and "View Code"
Copy/paste into that module, Edit the range to suit. Alt + q to return to
the Excel window.
Gord Dibben MS Excel MVP