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

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

--
Don Guillett
SalesAid Software

"Completely Confused" wrote
in message ...
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

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

Jim Thomlinson


"Completely Confused" wrote:

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

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

--
HTH...

Jim Thomlinson


"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Completely Confused" wrote
in message ...
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