Cell Formats - Limit # of characters
in [General]
|
From: KKD on 1 Apr 2010 18:01 I want to limit the # of characters allowed in a cell to 11, regardless of what the user types in (less characters is fine). How can I do this? -- KKD
From: Paul on 1 Apr 2010 18:10 Use Data Validation, and allow Text Length -> Less than or equal to -> 11. You can add information on the Error Alert tab to notify/prevent users from entering longer data. -- Paul - Paul ------------------------------------------------------------------------ Paul's Profile: 1697 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192385 http://www.thecodecage.com/forumz
From: Gord Dibben on 1 Apr 2010 18:38 Do you want a message telling user he has exceeded the 11 characters and make him do it over? Use Data Validation>Text Length. You can truncate automatically to 11 or less using event code. No messages or do-overs. Private Sub Worksheet_Change(ByVal Target As Range) Dim oval As String On Error GoTo ws_exit: Application.EnableEvents = False oval = Target.Value If oval = "" Then Exit Sub If Not Intersect(Target, Columns("A")) Is Nothing Then With Target If Len(oval) > 11 Then .Value = Left(oval, 11) End If End With End If ws_exit: Application.CutCopyMode = False Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 15:01:01 -0700, KKD <KKD(a)discussions.microsoft.com> wrote: >I want to limit the # of characters allowed in a cell to 11, regardless of >what the user types in (less characters is fine). How can I do this?
From: KKD on 1 Apr 2010 19:18 Can I copy data into a new cell and force it to drop off any additional characters beyond 11? Some form of truncating? -- KKD "Paul" wrote: > > Use Data Validation, and allow Text Length -> Less than or equal to -> > 11. You can add information on the Error Alert tab to notify/prevent > users from entering longer data. > > > -- > Paul > > - Paul > ------------------------------------------------------------------------ > Paul's Profile: 1697 > View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192385 > > http://www.thecodecage.com/forumz > > . >
From: Fred Smith on 1 Apr 2010 21:45
Certainly: =left(a1,11) Regards, Fred "KKD" <KKD(a)discussions.microsoft.com> wrote in message news:2C1A334D-50A0-4977-BEE4-320127748F2A(a)microsoft.com... > Can I copy data into a new cell and force it to drop off any additional > characters beyond 11? Some form of truncating? > -- > KKD > > > "Paul" wrote: > >> >> Use Data Validation, and allow Text Length -> Less than or equal to -> >> 11. You can add information on the Error Alert tab to notify/prevent >> users from entering longer data. >> >> >> -- >> Paul >> >> - Paul >> ------------------------------------------------------------------------ >> Paul's Profile: 1697 >> View this thread: >> http://www.thecodecage.com/forumz/showthread.php?t=192385 >> >> http://www.thecodecage.com/forumz >> >> . >> |