Cell Formats - Limit # of characters

  • Thread starter Thread starter KKD
  • Start date Start date
K

KKD

I want to limit the # of characters allowed in a cell to 11, regardless of
what the user types in (less characters is fine). How can I do this?
 
Do you want a message telling user he has exceeded the 11 characters and
make him do it over?

Use Data Validation>Text Length.

You can truncate automatically to 11 or less using event code.

No messages or do-overs.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oval As String
On Error GoTo ws_exit:
Application.EnableEvents = False
oval = Target.Value
If oval = "" Then Exit Sub
If Not Intersect(Target, Columns("A")) Is Nothing Then
With Target
If Len(oval) > 11 Then
.Value = Left(oval, 11)
End If
End With
End If
ws_exit:
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
 
Can I copy data into a new cell and force it to drop off any additional
characters beyond 11? Some form of truncating?
 
Certainly:
=left(a1,11)

Regards,
Fred

KKD said:
Can I copy data into a new cell and force it to drop off any additional
characters beyond 11? Some form of truncating?
 
Back
Top