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.

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.

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.

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.

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.

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