Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get characters on left of specified character in Excel | Excel Discussion (Misc queries) | |||
Auto-ID and Delete/left shift cells when a cell contains text? | Excel Discussion (Misc queries) | |||
auto delete characters in cell left of @ sign | Setting up and Configuration of Excel | |||
HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions | |||
Left three text characters | Excel Programming |