This will point you in the right direction. You will need to make some
changes to fit what you need, such as having the final value go where
you want it, and an additional loop statement.

Dim sNumber As String

Dim sName As String

Dim sChr As String

Dim iLen As Integer

Dim i As Integer

Dim sValue As String



sValue = Range("B1").Value 'Place this within a loop statement, where
you move from row to row getting the next value

iLen = Len(sValue)



For i = 1 To iLen

sChr = Mid(sValue, i, 1)

If IsNumeric(sChr) Or sChr = "-" Then

sNumber = sNumber & sChr

Else

sName = sName & sChr

End If

Next