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

Dim lngNum as Long
Dim sStr as String
Dim sStr1 as String

sStr1 = " tree1234 "
sStr = Left(sStr1,5)
lngNum = cLng(Trim(Right(sStr1,5)))

--
Regards,
Tom Ogilvy

"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

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

Given I don't know what all possible values are, I will assume that
tree1234 will always have the same length. Given that, you could try
the following

Public Function doParse(Text As String, RowNdx As Integer)
Range("B" & RowNdx) = Left(Text, 4)
Range("C" & RowNdx) = Right(Text, 4)
End Function

Private Sub Workbook_Open()
Dim RowNdx As Integer

Range("A1:A5").Select
With ActiveSheet.UsedRange
For RowNdx = 1 To 5
doParse Range("A" & RowNdx), RowNdx
Next
End With
End Sub

Given the following information in your spreadsheet (cells A1:A5):
tree1234
tree1235
tree1236
tree1237
tree1238



"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