delete left 8 characters in many cells

J

jposner

I'd like to ask if someone could provide a macro that would do th
following.

I have several rows in one column.

Looks like:

2000001_JSJSLSLS.tif

I'd like to just be able to run through the entire column (approx 630
rows) and delete the 8 Left characters while leaving the remainin
characters in the cell.

Result like:

JSJSLSLS.tif


Any help appreciated.

Thanks
 
P

pikus

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
y = 5 ‘The number of the column
For x = 1 To lrow
ActiveSheet.Cells(x, y).Value = Mid(ActiveSheet.Cells(x, y).Value, 9)
Next x

- Piku
 
J

JE McGimpsey

One way:

Public Sub DeleteFirst8()
Dim rCell As Range
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
.Value = Mid(.Text, 9)
End With
Next rCell
End Sub
 
D

Debra Dalgleish

One other way:

Sub RemoveLeftEight()

Columns("A:A").TextToColumns Destination:=Range("A1"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(8, 1))
End Sub
 
D

Debra Dalgleish

Thanks Ron.
It could also be modified to work on a selection instead of column A:

Sub RemoveLeftEightSelection()
Selection.TextToColumns _
Destination:=Selection.Cells(1.1), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(8, 1))
End Sub
 
Top