Limiting the number of characters in a cell

[smartgal]

Isn't there a way to define the number of characters a cell will allow? Our
operating system has fields that have defined character lengths, we need the
form our folks fill out to have the same limits.

Thanks!

 

[MyVeryOwnSelf]

Isn't there a way to define the number of characters a cell will

allow? Our operating system has fields that have defined character
lengths, we need the form our folks fill out to have the same limits.

One way with Excel 2003 is:
Data Validation > Settings >
Allow: Text length : Maximum
Data: Less than or equal to

Caution: while this prevents typing in too much text, it doesn't prevent
pasting it in.

 

[T. Valko]

Check out Data>Validation>Allow>Text length

 

[Luke M]

Under Data - Validation.
You can either use the preset dropdown of "Text Length" or choose custom and
use a formula similar to
=LEN(A2)=5

 

[YESHWANT]

Hi smartgal,

you can do the same thru :
open your excel file, click on data - validation - in the setting tab, in
"allow" field select "text lenth" - data field "not equal to" - length field,
enter the desired length - On the "error alert" tab, Style field "Stop" -
Enter your appropriate message in Title and error message fields - click OK
and your job is done.

click yes below, if it helps

 

[Gord Dibben]

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