Limiting a Column to "X" Number of Characters
 

I can't find a way to edit my original question, so I'm restating it here.
Marcelo gave me a great response, but what I want is to limit it to 33
characters even if there are 100 characters in the response. What happens
with the Data--Validation is that if you enter a response over 33, if rejects
the response. I want it to take the first 33 characters and enter that
information and ignore the rest. I hope this makes sense.

Thanks,

Wayne

Limiting a Column to "X" Number of Characters
 

Use one column for the full repsonse and a formula to extract the first 33
characters of the response:

=LEFT(A1,33)

Where A1 is the cell containing the value and 33 is the number of characters
to extract. If the response is under 33 you get the full value and if over
only the first 33 characters.
--
Kevin Backmann


"Wayne" wrote:

I can't find a way to edit my original question, so I'm restating it here.
Marcelo gave me a great response, but what I want is to limit it to 33
characters even if there are 100 characters in the response. What happens
with the Data--Validation is that if you enter a response over 33, if rejects
the response. I want it to take the first 33 characters and enter that
information and ignore the rest. I hope this makes sense.

Thanks,

Wayne

Limiting a Column to "X" Number of Characters
 

I sorry I do not have a correct understand of your question.


--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Wayne" escreveu:

I can't find a way to edit my original question, so I'm restating it here.
Marcelo gave me a great response, but what I want is to limit it to 33
characters even if there are 100 characters in the response. What happens
with the Data--Validation is that if you enter a response over 33, if rejects
the response. I want it to take the first 33 characters and enter that
information and ignore the rest. I hope this makes sense.

Thanks,

Wayne

Limiting a Column to "X" Number of Characters
 

Excel doesn't offer this kind of feature.

There are other options, though.

#1. You could use one cell for the input and another cell to retrieve the first
33 characters:
=left(a1,33)

#2. You could use an event macro that would truncate any entry to 33 characters
or less. If you want to try this, rightclick on the worksheet that should have
this behavior. Select View Code and paste this into the code window that just
opened.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim MaxLen As Long

MaxLen = 33

'one cell at a time!
If Target.Cells.Count 1 Then Exit Sub

'only in column A:
If Intersect(Target, Me.Range("A:A")) Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:

If Len(Target.Value) MaxLen Then
Application.EnableEvents = False
Target.Value = Left(Target.Value, 33)
End If

ErrHandler:
Application.EnableEvents = True
End Sub

Then back to excel to test it out.

Remember that this converts anything to the first 33 characters--it'll convert
formulas that evaluate to longer than 33 characters to values of 33 characters.

#3. You could design a userform to do the data entry.

Debra Dalgleish has some getstarted instructions for userforms at:
http://contextures.com/xlUserForm01.html


Wayne wrote:

I can't find a way to edit my original question, so I'm restating it here.
Marcelo gave me a great response, but what I want is to limit it to 33
characters even if there are 100 characters in the response. What happens
with the Data--Validation is that if you enter a response over 33, if rejects
the response. I want it to take the first 33 characters and enter that
information and ignore the rest. I hope this makes sense.

Thanks,

Wayne

--

Dave Peterson