truncate text in each cell in column

truncate text in each cell in column

Post by SmFrZ » Sat, 29 Nov 2008 00:06:00


Hello,
I would like to loop through text data in a column and truncate the cells to
35 characters. I don't know how many cells will contain data and there are
empty cells. I can have 50,000 or more cells in the column. I thought this
code would work:
For Each rngCell In Columns("N:N")
rngCell.Value = Left(CStr(rngCell), 35)
Next rngCell
but it gives a type mismatch error.
Also this doesn't loop through each cell in the column as I thought it would.
Thanks for any help.
Jake
 
 
 

truncate text in each cell in column

Post by Chip Pears » Sat, 29 Nov 2008 00:15:18


The problem is that rngCell is being set to then entire column N. Try

For Each rngCell In Columns("N:N").Cells

or, better,

For Each rngCell In Application.Intersect( _
ActiveSheet.UsedRange, ActiveSheet.Columns("N")).Cells

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Thu, 27 Nov 2008 07:06:00 -0800, Jake

 
 
 

truncate text in each cell in column

Post by JE McGimps » Sat, 29 Nov 2008 00:23:36

One way:

Dim rngCell As Range

For Each rngCell In Range("N1:N" & _
Range("N" & Rows.Count).End(xlUp).Row)
With rngCell
If Not IsEmpty(.Value) Then _
.Value = Left(.Text, 35)
End With
Next rngCell



In article < XXXX@XXXXX.COM >,
 
 
 

truncate text in each cell in column

Post by SmFrZ » Sat, 29 Nov 2008 00:57:01

Works fine. Thanks!