Limiting the number of characters in a cell

S

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!
 
M

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.
 
L

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
 
Y

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top