Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit the number of characters in a cell?
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit the number of characters in a cell?
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 "filpass" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit the number of characters in a cell?
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) On Wed, 17 Dec 2008 14:04:19 -0800, filpass wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit the number of characters in a cell?
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 "Mike H" wrote: 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 "filpass" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit the number of characters in a cell?
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... "Chip Pearson" wrote: 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) On Wed, 17 Dec 2008 14:04:19 -0800, filpass wrote: 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to limit the number of characters in a cell?
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 On Wed, 17 Dec 2008 14:04:19 -0800, filpass wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limit the number of characters entered in a single cell | Excel Discussion (Misc queries) | |||
LIMIT THE NUMBER OF CHARACTERS ENTERED IN A FIELD | Excel Worksheet Functions | |||
Urgent help needed? How do I limit the number of characters in a | Excel Discussion (Misc queries) | |||
Limit the number of characters that a cell contains | Excel Worksheet Functions | |||
limit number of characters in a cell | Excel Discussion (Misc queries) |