truncating pasted text after 250 characters

  • Thread starter Thread starter sup191
  • Start date Start date
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
 
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
 
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+
 
I'm still having problems figuring this problem out. Can anyone pleas
help shed some light on it?

Thanks again
 
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...
 
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...
 
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
 
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
 
Back
Top