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]

You could use a macro if you really want to, but the easiest way would be just to use a formula ie =Right(A1,Len(A1)-4) , and copy/pastespecial the values onto the original range.

If you want a macro, paste the code that you already have, and I'll help you out.

 

[Norie]

Derek

Do you really need a macro to do this?

Can you not use worksheet functions like MID, LEFT and RIGHT?

What do you mean by a list of files, are they in cells on a worksheet?

Can you post what code you are currently using and an explaination what you want it do do?

 

[Nimrod]

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


............ :wink:

 

[krazyderek]

yup each name is in it's own cell, going down on colum. but each name has numbers next to it like this..

21. buddy dude guy
22. doodedoodedoo
23. lalalalal

i just need something that will quickly delete the 2 numbers, the decimal, and the space and just leave me with this...


buddy dude guy
doodedoodedoo
lalalalal

doesn't have to be a macro, just thought that was the easiest way to do it as i'm fairly unfamiliar with excel other then a couple of math formulas...

oh ya... there's about 80 cells..

 

[sweater_vests_rock]

Derek.

If the cells are non-formulaic,

Sub DeleteFirstFour()
For Each C In Cells.SpecialCells(xlCellTypeConstants)
If C.Value <> "" Then
C.Value = Mid(C.Value, 5, Len(C.Value) - 4)
End If
Next C
End If

WARNING!!! This deletes the first four from ALL cells. Change Cells. to Range("A:A"). for the first column only.

 

[krazyderek]

oh cool 2 different ways. ok i'll give them a try now, thank you very much !!

 

[CraigM]

Just a thought.... Should you not really delete all the characters up until
, and including, the space? You say your list is about 80 long. If it increases (is this possible?) to over 99 then, I assume, you will want to delete the first 5 characters of some cells ie "123. Blahblah"

 

[Norie]

Derek

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

 

[krazyderek]

it's just a list of 98, and i can just do the first 10 on my own....

hmmm is there a more complexe one that will remove the single, double and triple digit numbers from a list? now that i think about it, chances are next time the list is going to be much bigger... only problem is there may be numbers mixed in with the names them selves too..