Macro for deleting characters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shane - 24 Nov 2006 07:51 GMT

Hi

I am trying to find a way to delete the first 5 characters in a cell, in
about 1700 cells, hence why I want avoid the "manual" process...

I have tried doing this with a Macro, but all the Macro does is replace the
text with what was recorded, it doesnt delete the spaces. (The way I
recorded the Macro was "F2, HOME, del, del, del, del, del, ENTER".

Can anyone suggest where I am going wrong please, or an alternative way of
doing this?

Thanks in advance!

Shane

Reply to this Message

Big Brother - 24 Nov 2006 08:34 GMT

See below

Sub ZZZ()
Dim R As Range
For Each R In Range("a1:a1700")
R.Value = Right(R.Value, Len(R.Value) - 5)
Next R
End Sub

> Hi
>
[quoted text clipped - 10 lines]
>
> Shane

Reply to this Message

Bob Phillips - 24 Nov 2006 09:03 GMT

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
For i = 1 To iLastRow
Cells(i,"A").Value = Mid(Cells(i,"A").Value,6)
Next i

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

> Hi
>
[quoted text clipped - 11 lines]
>
> Shane

Reply to this Message

Lori - 24 Nov 2006 09:12 GMT

If data is in a column, you could select the column and choose Data >
Text to Columns:
Step 1. Fixed Width
Step 2. Vertical line at 5h character
Step 3. Set first column to Skip.

> Hi
>
[quoted text clipped - 11 lines]
>
> Shane

Reply to this Message