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

J

Bob Umlas seems to have this answered very well a
http://www.emailoffice.com/excel/arrays-bobumlas.html.

Hope it helps

--
Kiera

Born ignorant and still perfecting the ar
-----------------------------------------------------------------------
Kieran's Profile: http://www.excelforum.com/member.php...nfo&userid=124
View this thread: http://www.excelforum.com/showthread.php?threadid=27407

if you have excel 2000, use InstrRev

iloc = InstrRev(cell.Value," ")
lastword = right(cell.Value,Len(cell.value)-iloc)
--
Regards,

"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

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

Looks like there's already some answers here, but I'll throw mine out there
for you also (paste into a module and use it as a function in the workbook):

Function RIGHTWORD(rng As Range)

If rng.Cells.Count 1 Then RIGHTWORD = CVErr(xlErrValue)

If IsEmpty(rng.Value) Then RIGHTWORD = ""

RIGHTWORD = Right(rng.Value, Len(rng.Value) - InStrRev(rng.Value, " "))

End Function

-erik

"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

On 31 Oct 2004 19:53:16 -0800, (jh) wrote:

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

Here is a worksheet formula that will accomplish that.

=IF(ISERROR(FIND(" ",A1)),A1,RIGHT(A1,LEN(A1)-
FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))))))


--ron