Moving cell data left

[themiz]

Hi there, thank you in advance for any and all help.

In the attached wrkbook, you'll see columns with "##### - Name". I wan
to separate the two values (the numbers and the description) and mov
the numbers to column A. I have been using a macro to separate the cel
data and was wondering if someone could help me insert a line for movin
the (new separated) "#####" data to column A.


Thanks again

+-------------------------------------------------------------------
|Filename: Book1.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=749
+-------------------------------------------------------------------

 

[Auric__]

Hi there, thank you in advance for any and all help.

In the attached wrkbook, you'll see columns with "##### - Name". I want
to separate the two values (the numbers and the description) and move
the numbers to column A. I have been using a macro to separate the cell
data and was wondering if someone could help me insert a line for moving
the (new separated) "#####" data to column A.

Your file doesn't have anything in that format.

If the info is *always* in that format, rather than using Like, Split, etc.,
as in the code attached to that file (and also avoiding the need for 3
separate subs) I'd just do something like this:

Sub colNew()
Dim r As Range
For Each r In Range("D1:F" & _
Cells.SpecialCells(xlCellTypeLastCell).Row)
tmp$ = r.Value
where = InStr(tmp$, " - ")
If where Then
numpart = Left$(tmp$, where - 1)
namepart = Mid$(tmp$, where + 3)
Cells(r.Row, 1).Value = numpart
Cells(r.Row, 2).Value = namepart
End If
Next
End Sub

The targets for numpart and namepart will need to be verified by you.