Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
max number of spaces/letters
How do I program a cell to have let say a maximum word/sentence length of 50 spaces.:Bgr -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100198 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
max number of spaces/letters
You can do this with data validation
Select the cell(s) in question Goto the menu DataValidation AllowText length Fill in the info OK -- Biff Microsoft Excel MVP "willemeulen" wrote in message ... How do I program a cell to have let say a maximum word/sentence length of 50 spaces.:Bgr -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100198 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
max number of spaces/letters
Data-Data Validation-select Text length
set maximum to 50 or Data-Data Validation-select Custom in Formula window insert: =LEN(A1)=50 (this will allow up to 50 characters in A1) adjust address or copy to your range On 25 Maj, 19:36, willemeulen wrote: How do I program a cell to have let say a maximum word/sentence length of 50 spaces.:Bgr -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile:http://www.thecodecage.com/forumz/member.php?userid=285 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=100198 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
max number of spaces/letters
Using data validation for this many characters would be a PITA in my
estimation. DV won't automatically give you a maximum of 50, just a warning after you hit the ENTER key and you have to start all over again. I would prefer code that doesn't raise a an error if more than 50 chars are entered, just truncates to first 50 chars. 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) 50 Then .Value = Left(.Value, 50) 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 Mon, 25 May 2009 18:36:45 +0100, willemeulen wrote: How do I program a cell to have let say a maximum word/sentence length of 50 spaces.:Bgr |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
max number of spaces/letters
"Start all over again" is an exaggeration but you do have hit "retry" then
click in formula bar and edit back down to 50 or less. Gord On Mon, 25 May 2009 13:44:22 -0700, Gord Dibben <gorddibbATshawDOTca wrote: DV won't automatically give you a maximum of 50, just a warning after you hit the ENTER key and you have to start all over again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to convert a number with spaces to just a number? | Excel Discussion (Misc queries) | |||
how do I reduce the spaces between letters in a word? | Excel Discussion (Misc queries) | |||
number of spaces... | Excel Worksheet Functions | |||
COUNT THE NUMBER OF LETTERS INCLUDING SPACES IN A CELL? | Excel Worksheet Functions | |||
can you count the number of letters (inc spaces) in a single sell | New Users to Excel |