How to limit the number of characters in a cell?

[filpass]

Dear Excel forum members,

I would like to limit the number of characters in a cell. How shall I do?

If a name is 50 characters long and I set up the limit at 30, will the 30
characters appear or will I receive an error message?

Thanks a lot for your help!

 

[Mike H]

Hi,

data validation will reject the input but you can do it like this. Right
click your sheet tab, view code and paste this in. It works on column 1 (a)
change to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
On Error GoTo getmeout
If Target.Characters.Count > 30 Then
MsgBox "No more than 30 characters. text has been truncated"
Target.Value = Left(Target.Value, 30)
End If
getmeout:
End Sub

Mike

 

[Chip Pearson]

You can use Data Validation from the format menu. In the Validation
dialog, choose Text Length in the Allow list and then choose Less Than
and enter the limit. The user will be able to type as many characters
as he wants, but it won't be accepted into the cell. A message box
will pop up informing the user of the error of his ways.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[filpass]

Hi Mike,

thanks for your help! It kind of works, but not perfectly. I still need to
click on each cell in order to see the text become truncated. I have a list
of 18'000 cells, so I cannot click on each of them... Ideally I would like to
copy/paste and see them truncated.

Do you have any idea how to proceed?

Thanks!

Filippo

 

[filpass]

Hi Chip,

thanks for your reply!

your method works if I enter a new value (text), however if I copy/paste a
long value then it is accepted...

I am still stuck...

 

[Gord Dibben]

You will receive an error message if you exceed the limit.

Assuming you have used Data Validation.

If you want the limit at 30 with no error message you would need VBA event
code.


Gord Dibben MS Excel MVP