You could try this. It works on selected cells, so select your cells and
then run the macro. It will populate the next righthand cell with the
text part and the second right hand cell with the number part.

See how you get on with it.

Sub a()
Dim rng As Range
Dim i As Integer

Dim strRng As String
Dim str As String
Dim strOutAlpha As String
Dim strOutNum As String

Dim j As Integer
Dim IsAlpha As Boolean

For Each rng In Selection
i = 0
j = 0
strRng = rng
strOutAlpha = ""
strOutNum = "'"
IsAlpha = True

For j = 1 To Len(strRng)
str = Mid(strRng, j, 1)
Select Case str
Case "0" To "9"
IsAlpha = False
strOutNum = strOutNum & str
Case " "
If IsAlpha Then
strOutAlpha = strOutAlpha & str
End If
Case Else
IsAlpha = True
strOutAlpha = strOutAlpha & str
End Select
Next
rng.Offset(, 1) = strOutAlpha
rng.Offset(, 2) = strOutNum
Next
MsgBox "done"
End Sub


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=564613