simple macro help (but i'm a newb)

[krazyderek]

i have a list of files in excel, i just want to get rid of the numbers at the begining of the cell. so i tried to make a macro that takes a cell and just delet's the first 4 characters then goes on to the next. What happened was it did do this, but it kept replacing what was in the next cell with what was in the very first cell when i recorded the macro.... not really sure how to make a macro start by accepting what ever "may" be in the cell and just making it deleting the first 4 characters in the cell.

Thanks, Derek

 

[CraigM]

Here is a modification of Nimrod's code above, which will remove all characters to the left of the first (if any) period (.):

Sub Left4Trim()
For Each strng In Columns("A:A").SpecialCells(xlCellTypeConstants, 3)
    n = InStr(1, strng.Value, ".")
    If n <> 0 Then strng.Value = Trim(Right(strng, Len(strng) - n))
Next strng
End Sub

 

[krazyderek]

Derek

Why not try Data>Texts to Column... Delimited and select Other and enter '.'?

hmm that also kind of worked.. but these are file names.. and have several dots in them, sometimes dots are used instead of spaces... the output just seems to be a bit to messy.. thanks anyways it was interesting to try

 

[Nimrod]

This is flexable ... it will determine where the period is ... :wink:

Sub Left4Trim()
For Each Strng In Columns("A:A").SpecialCells(xlCellTypeConstants, 3)
PeriodPosition = InStr(1, Strng, ".")
Strng.Value = Right(Strng, Len(Strng) - (PeriodPosition + 1))
Next Strng
End Sub

 

[krazyderek]

cool thanks craig!!!!!! so... i'm guessing there's no saving in visual basic?? i just pasted it in there (editing my original one) and closed it and it worked..

 

[CraigM]

It will be saved when you save the excel workbook.