Extract date from right to left in a cell

[jh]

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

 

[Kieran]

J

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

Hope it helps

 

[Tom Ogilvy]

if you have excel 2000, use InstrRev

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

 

[Norman Jones]

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.

 

[E Oveson]

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

 

[Ron Rosenfeld]

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