Hi JH,

Try:

Sub Tester()
Dim sStr As String, oldStr As String
Dim iPos As Long
Dim Rng As Range, Rng2 As Range, rCell As Range

Set Rng = Range("A1:A100") '<<=== CHANGE
On Error Resume Next
Set Rng2 = Rng.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0
If Not Rng2 Is Nothing Then
For Each rCell In Rng2
oldStr = rCell.Text
iPos = InStrRev(oldStr, " ")
sStr = Mid(oldStr, iPos + 1)
MsgBox sStr
Next rCell
End If
End Sub

Change Rng = Range("A1:A100") to suit your needs.

---
Regards,
Norman



"jh" wrote in message
om...
I have an excel spreadsheet containing numerous cells. I need to
extract the right most word in each cell. The lengths and number of
spaces vary between cells, so I need a function that will read from
right to left until a space is encountered. Any help will be greatly
appreciated.

J