delete left 8 characters in many cells
 

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

--
Message posted from http://www.ExcelForum.com

delete left 8 characters in many cells
 

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

--
Message posted from http://www.ExcelForum.com

delete left 8 characters in many cells
 

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


In article ,
jposner wrote:

I'd like to ask if someone could provide a macro that would do the
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 6300
rows) and delete the 8 Left characters while leaving the remaining
characters in the cell.

Result like:

JSJSLSLS.tif


Any help appreciated.

Thanks!


---
Message posted from http://www.ExcelForum.com/

delete left 8 characters in many cells
 

One other way:

Sub RemoveLeftEight()

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


jposner < wrote:
I'd like to ask if someone could provide a macro that would do the
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 6300
rows) and delete the 8 Left characters while leaving the remaining
characters in the cell.

Result like:

JSJSLSLS.tif


Any help appreciated.

Thanks!


---
Message posted from http://www.ExcelForum.com/



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

delete left 8 characters in many cells
 

I like that one Debra

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Debra Dalgleish" wrote in message ...
One other way:

Sub RemoveLeftEight()

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


jposner < wrote:
I'd like to ask if someone could provide a macro that would do the
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 6300
rows) and delete the 8 Left characters while leaving the remaining
characters in the cell.

Result like:

JSJSLSLS.tif


Any help appreciated.

Thanks!


---
Message posted from http://www.ExcelForum.com/



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

delete left 8 characters in many cells
 

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

Ron de Bruin wrote:
I like that one Debra



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html