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