deleting from within a cell

[Guest]

I have a column of 5000 part numbers. In each cell contains a part number.
The part numbers looks like this...

1240-00-101-1234
1240-00-102-1234
1240-00-103-1234

I am trying to delete from each cell the dash -
and the first four numbers of the part number so it will look like this

001011234
001021234
001031234

 

[Don Guillett]

Sub cleanupnumbers()
'preformat range as text or do it in macro
For Each c In Range("a2:a4")
ms = Right(c, Len(c) - 4)
c.Value = Application.Substitute(ms, "-", "")
Next
End Sub

 

[Guest]

Here is a fomula to do it...
=MID(A1, 6, 2) & MID(A1, 9, 3) & RIGHT(A1, 4)

 

[Guest]

Note that this method is going to result in a number not a text string,
unless you have formatted the cells as text in advance as suggested. If it is
left a number it is going to drop the two leading zeros. The code will also
not work if you have option explicit at the top of the module.
Here is the code just tweaked a bit... delete the format you do not want...

Sub cleanupnumbers()
dim c as Range
'preformat range as text or do it in macro
For Each c In Range("a2:a4")
c.numberformat = "@" 'for Text
c.numberformat = "000000000" 'for a number
c.Value = Application.Substitute(Right(c, Len(c) - 4), "-", "")
Next c
End Sub