Can I restrict a text column to a specific number of characters?

  • Thread starter Thread starter Irene Threadgold
  • Start date Start date
I

Irene Threadgold

Is there a way in which I can restrict the number of characters entered in an
excel spreadsheet column, eg, maximum 50 characters.
 
Hi,

Select the column then

data|Validation

From the drop down select 'Text Length' and set you Min and max values

Mike
 
Thank you very much

Mike H said:
Hi,

Select the column then

data|Validation

From the drop down select 'Text Length' and set you Min and max values

Mike
Click to expand...
 
The only problem with Data Validation is that you get an error message if
you exceed the 50 characters and you have to edit to get back to 50 or less.

You could set up event code to truncate anything over a certain number of
characters after user hits ENTER key

No message, no editing.

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) > 50 Then
.Value = Left(.Value, 50)
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

On Thu, 5 Nov 2009 07:34:06 -0800, Irene Threadgold <Irene
 
Back
Top