Predefined Cell Character Length

[Matt]

Hi,

I'm sure there is a way to do this but I am not to versed in excel. So I
have some data i'm pulling out a database to clean up but I want to make sure
users cannot enter more characters then i allow for a column/cell. i.e. Al
cells in clumn A can only be 10 characters at the most, Column be up to 30
etc.

It would be best for all excel versions but mostly will be used in excel 2003.

Thanks,
Matt

 

[Gord Dibben]

You could use Data Validation and limit Text Length to how many characters
you wish.

Unfortunately this requires users to repeat their input until they get it
right.

Very annoying to me.

I would prefer event code which simply truncates any entries to a maximum
character count automatically.

Here is sample of what I mean.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A100" '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) > 10 Then
.Value = Left(.Value, 10)
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

 

[Gord Dibben]

One more method.

In a helper column enter =LEFT(A1,10)

Copy down as far as you wish.

As users enter data in column A, the data will return only 10 chars in
column C

You can then copy column C and Paste Special>Values over Column A


Gord

 

[Matt]

Thanks for your help Gord I will try it out. probably the 2nd one, the 1st
one was too advanced for me.

it does sound like these options keep the character count to the number
specified but i was looking to a cut off but with no limit to anything under
10 characters. Meaning we may have an item that is 3 char, one that is 7 char
anoother that is 2 char so those would all be valid entries. They would only
be stopped from ever enter 11 char item #.

 

[Gord Dibben]

Matt

Whichever method you use..........event code or formula, will account for
cells with characters less than 10

=LEFT(A1,10) returns up to 10 characters.

If 3 chars in a cell just those 3 will be returned.

And what's advanced about installing the event code per my instructions?
Other than editing ranges<g>

If you have more than one column to truncate to a different size, I can post
code for that.

If you use the formula method on another column that requires 20 chars, just
change the =LEFT(A1,10) to something suiting the column reference and number
of chars.

i.e. =LEFT(D1,20)


Gord