Find last space from the right of text

M

Michael

I need to find the last space from the right of text in a cell and then
return the text that is to the right of that last space.

Any help is greatly appreciated.

Thanks
 
D

Dave O

Check out MID(), FIND(), SEARCH(), and RIGHT() functions. Can you
provide an example or two of your data?
 
M

Myrna Larson

=MID(SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))),FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)
 
D

Don Guillett

Put this function in a REGULAR module and then =fls(d10) will work.
Function FLS(X)
FLS = Right(X, Len(X) - InStrRev(X, " "))
End Function

A macro to do that for the active cell.
Sub FLSS()
MsgBox Right(ActiveCell, Len(ActiveCell) - InStrRev(ActiveCell, " "))
End Sub
=====
if your version does not have instrev then use this

Function InStrRev(Strng As String, Char As String) As Integer
Dim Lngth As Integer, i As Integer
Lngth = Len(Strng)
For i = Lngth To 1 Step -1
If Mid(Strng, i, 1) = Char Then
InStrRev = i
Exit Function
End If
Next i
End Function
'Howard Groves cmmroom@ ddre.detroitdiesel.com
=========
 
R

Ron Rosenfeld

I need to find the last space from the right of text in a cell and then
return the text that is to the right of that last space.

Any help is greatly appreciated.

Thanks
Click to expand...


Assuming that there are no tilde's in your original text:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,1024)

will do what you describe.


--ron
 
G

gennario

Ron,

Your resonse on this was perfect for what I am looking for. Is there also a
way to return the data BEFORE (or the left) of the space?
 
D

Don Guillett

Think about it and you will be able to come up with the answer yourself.
Best to keep responses in the ORIGINAL thread for continuity.
 
Top