Cell Formats - Limit # of characters

[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?

 

[Gord Dibben]

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

 

[KKD]

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

 

[Fred Smith]

Certainly:
=left(a1,11)

Regards,
Fred

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