Need macro to trim characters in column
 

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.

Need macro to trim characters in column
 

something like this

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"TonyV" wrote in message
...
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.

Need macro to trim characters in column
 

Sub trimit()

Dim cell As Excel.Range

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

End Sub


HTH,
JP

On Jan 10, 11:21*am, TonyV wrote:
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.

Need macro to trim characters in column
 

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

"TonyV" wrote:

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.

Need macro to trim characters in column
 

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

"TonyV" wrote:

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.

Need macro to trim characters in column
 

...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

"TonyV" wrote:

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.