Need macro to trim characters in column

[TonyV]

I have a 100 lines in column A in my excel 2003 spreadsheet, each containing
various lengths of text, and need to trim each cell to contain the first 21
characters only & discard the rest.

I will be greatful if someone can assist me in writing a macro to get this
done that will be equivalent the LEFT formula.

Thankyou.

 

[Don Guillett]

something like this

for each c in range("a2:a102")
c.value=left(c,len(c)-21)
next

 

[JP]

Sub trimit()

Dim cell As Excel.Range

For Each cell In Selection
cell = Left(LTrim(cell), 21)
Next cell

End Sub


HTH,
JP

 

[FSt1]

hi.
try this
Sub trimit()
Dim r As Range
Dim rd As Range
Set r = Range("A2") 'assumes header
Do While Not IsEmpty(r)
Set rd = r.Offset(1, 0)
r.Value = Left(r, 21)
Set r = rd
Loop
End Sub

regards
FSt1

 

[Nigel RS]

Sub FiftyChars()
Dim c As Range
With Sheets(1) '<<<<= change to suit
For Each c In .Range("A1:A100") '<<<= change to suit
c.Value = Left(c, 2)
Next
End With
End Sub

 

[Nigel RS]

...oops sorry 21 in the left function below not 2 !

Sub FiftyChars()
Dim c As Range
With Sheets(1)
For Each c In .Range("A1:A100")
c.Value = Left(c, 21)
Next
End With
End Sub