Extract data at end of cell

[madsc1]

Columns:
First_Name Last_Name Address City Postal

Working with just the address data, example: address cell value 123 Any Street 201, 201 being the apartment number, 123 the street address.. This is the VBA code to extract apartment number and move data 1 column to the right into an empty column. My problem is it is picking the front of the address eg 123, not the apartment # eg 201. New to VBA so I'm not sure how to get it to extract from the right. Anywhere I insert the right qualifier, I get compile errors. Any ideas!

Sub SplitAddresses()
Dim s As String
Dim i As Integer
Dim cell As Range
For Each cell In Range("E:E").Cells


s = cell.Value
If s = "" Then Exit Sub
i = InStr(s, " ")
If i > 0 Then
cell.Offset(0, 1).Value = Mid(s, i + 1)
cell.Value = Left(s, i - 1)
End If
Next
End Sub

Thanks.

[ssanfu]

To search from the right, use InStrRev() .

[madsc1]

Much Thanks,
Peter