Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
truncating pasted text after 250 characters
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
truncating pasted text after 250 characters
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 -- Regards Frank Kabel Frankfurt, Germany How would I go about cutting off text in a pasted column and keeping only the first 250 characters of each cell? Validating the column doesn't truncate like I want and nobody is entering data directly into the spreadsheet - it's all copy and pasting. I've tried using left functions, but I'm failing miserably. Can somebody please help shed some light on this problem. I'm guessing it's probably a 2-3 line answer and I'm overthinking for a solution. ;) Thanks! --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
truncating pasted text after 250 characters
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+ -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
truncating pasted text after 250 characters
I'm still having problems figuring this problem out. Can anyone pleas
help shed some light on it? Thanks again -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
truncating pasted text after 250 characters
Hi
what is your problem. A little bit more detail would be helpful :-) -- Regards Frank Kabel Frankfurt, Germany I'm still having problems figuring this problem out. Can anyone please help shed some light on it? Thanks again! --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
truncating pasted text after 250 characters
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 Kabel wrote: [b]Hi what is your problem. A little bit more detail would be helpful :- -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
truncating pasted text after 250 characters
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 Kabel wrote: [b]Hi what is your problem. A little bit more detail would be helpful :-) --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
truncating pasted text after 250 characters
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 -- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
truncating pasted text after 250 characters
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 -- Regards Frank Kabel Frankfurt, Germany I need to have the column automatically truncate the data as soon as it's pasted in column I. I've tried using the sub you posted by 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't understand what macros do, and I want to automate things to avoid any confusion that might come up. If I called the sub with the above statement, wouldn't that do the same trick as running the macro manually after the data is pasted? --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why is Excel truncating my text to 255 characters? | Excel Discussion (Misc queries) | |||
text truncating | Excel Discussion (Misc queries) | |||
Intentionally truncating text characters | Excel Discussion (Misc queries) | |||
truncating a colum to 55 characters | Excel Discussion (Misc queries) | |||
Exported File truncating cell data after 255 characters | Excel Discussion (Misc queries) |