Alter column with character limit

  • Thread starter Thread starter Charles
  • Start date Start date
C

Charles

Trying to alter two columns with existing data, resizing
the text in each cell to a maximum of 10 characters. I
could go to each cell and delete the characters over 10,
but there are almost 12k rows. I tried the Validation
feature, but that is only for new cells and will not
change ones that already have text in them.

Any suggestions would be much appreciated. Thanks.

Charles
 
Charles said:
Trying to alter two columns with existing data, resizing
the text in each cell to a maximum of 10 characters. I
could go to each cell and delete the characters over 10,
but there are almost 12k rows. I tried the Validation
feature, but that is only for new cells and will not
change ones that already have text in them.

Any suggestions would be much appreciated. Thanks.

Charles
Click to expand...

For data in (say) column A, put the formula
=LEFT(A1,10)
in B1 and copy down as far as required in column B.
Then you can do Copy/Paste Values to eliminate the formulas.
 
Paul,

Thanks!! That is a HUGE help. One thing I just found out
was that I need to eliminate spaces between words, as
well. Any suggestions there?

Charles
 
Sub TruncateCellContents()
While ActiveCell.Value <> ""
ActiveCell.Value = Mid(ActiveCell.Value, 1, 10)
ActiveCell.Offset(1, 0).Activate
Wend
End Sub

You may want a better test for when to quit.

You may want to wrap
Application.ScreenUpdating = false/true
around the code in order to speed up the
execution for a long list.

While code could be written to do both columns at once, doing each
column separately with the above is perhaps easier.

The pros out there will surely have more elegant code.

Fred Holmes
 
Just a tweak:

Public Sub TruncateCellContents()
Dim list As Variant
Dim i As Long
With Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row)
list = .Value
For i = 1 To UBound(list, 1)
list(i, 1) = Left(list(i, 1), 10)
Next i
.Value = list
End With
End Sub
 
Back
Top