Sub test()
Dim str As String, lng As Long, i As Long

str = "tree1234"
i = 1: Do Until IsNumeric(Mid(str, i, 1)): i = i + 1: Loop
lng = Right(str, Len(str) - i + 1)
str = Left(str, i - 1)
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Johnny" wrote in message
...
If I have a string " tree1234 ". How can I separate it
into 2 cells " tree " and the most important the " 1234 "
should be in value format. Pls advise.

Johnny