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!

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.

Check out DataValidationAllowText length

--
Biff
Microsoft Excel MVP


"smartgal" wrote in message
...
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!

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"smartgal" wrote:

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!

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


"smartgal" wrote:

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!

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

On Thu, 10 Sep 2009 10:51:59 -0700, smartgal
wrote:

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!