truncating pasted text after 250 characters

S

sup191

How would I go about cutting off text in a pasted column and keepin
only the first 250 characters of each cell? Validating the colum
doesn't truncate like I want and nobody is entering data directly int
the spreadsheet - it's all copy and pasting. I've tried using lef
functions, but I'm failing miserably. Can somebody please help she
some light on this problem. I'm guessing it's probably a 2-3 lin
answer and I'm overthinking for a solution. ;)

Thanks
 
F

Frank Kabel

Hi
try something like the following macro (will truncate the active
Selection):
Sub trunc_left()
Dim rng As Range
Dim c As Range
Set rng = Selection
For Each c In rng
c.Value = Left(c.Value, 250)
Next
End Sub
 
S

sup191

I've been trying a similar range function that looks something lik
this:

Sub TrimText()
Dim lLastRow As Long
Dim Sh1 As Worksheet, rng As Range

Set Sh1 = ThisWorkbook.Worksheets("Form")
lLastRow = Sh1.Cells(Rows.Count, "I").End(xlUp).Row
Set rng = Sh1.Range("I2", "I2" & lLastRow)

rng.Value = Left(rng.Value, 250)

Set Sh1 = Nothing
Set rng = Nothing
End Sub


However, nothing gets truncated when pasted in column I2+
 
S

sup191

I'm still having problems figuring this problem out. Can anyone pleas
help shed some light on it?

Thanks again
 
S

sup191

Sorry about that... It's still early and I'm not fully awake yet...

I used the formula you posted and the subroutine I posted above, bu
the text is still not being truncated when pasted into column I. Fo
example, let's say I have 10 rows of text being pasted into column
starting at I2. I1 is the column name header. All the information i
being pasted correctly, but nothing is being truncated.




Frank said:
Hi
what is your problem. A little bit more detail would be helpful :-
Click to expand...
 
F

Frank Kabel

Hi
try the following:
1. Insert this macro in one of your VBA modules
Sub trunc_left()
Dim rng As Range
Dim c As Range
Set rng = Selection
For Each c In rng
c.Value = Left(c.Value, 250)
Next
End Sub

2. Paste your values in column I
3. Select these cells and invoke the macro -> Cells will be truncated

The truncation will not take plase automatically when pasting but you
have to start the macro afterwards


--
Regards
Frank Kabel
Frankfurt, Germany
Sorry about that... It's still early and I'm not fully awake yet...

I used the formula you posted and the subroutine I posted above, but
the text is still not being truncated when pasted into column I. For
example, let's say I have 10 rows of text being pasted into column I
starting at I2. I1 is the column name header. All the information is
being pasted correctly, but nothing is being truncated.




Frank said:
Hi
what is your problem. A little bit more detail would be helpful :)
Click to expand...
Click to expand...
 
S

sup191

I need to have the column automatically truncate the data as soon a
it's pasted in column I. I've tried using the sub you posted b
calling it with:

If Not (Intersect(Target, Range("I:I")) Is Nothing) Then
Application.EnableEvents = False
TrimText
End If


The spreadsheet I'm editing is used by a lot of people who don'
understand what macros do, and I want to automate things to avoid an
confusion that might come up. If I called the sub with the abov
statement, wouldn't that do the same trick as running the macr
manually after the data is pasted
 
F

Frank Kabel

Hi
o.k. paste the following code in the worksheetmodule of the specific
worksheet (right-click on the tab name and choose 'Code')
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Intersect(Target, Me.Range("I:I")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
Application.EnableEvents = False
For Each c In Target
If c.Value <> "" Then
c.Value = Left(c.Value, 3)
End If
Next c
CleanUp:
Application.EnableEvents = True
End Sub
 
Top