Find last space from the right of text

[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

[Dave O]

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

[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)

On Mon, 24 Jan 2005 09:49:02 -0800, "Michael"
<[email protected]> wrote:

>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

[Michael]

Thanks to both of you for posting suggestions and answers. This was very
helpful.

"Myrna Larson" wrote:

> =MID(SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"
> ",""))),FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"
> ",""))))+1,255)
>
> On Mon, 24 Jan 2005 09:49:02 -0800, "Michael"
> <[email protected]> wrote:
>
> >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
>
>

[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
=========

--
Don Guillett
SalesAid Software
[email protected]
"Michael" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Ron Rosenfeld]

On Mon, 24 Jan 2005 09:49:02 -0800, "Michael"
<[email protected]> wrote:

>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


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

[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?

"Ron Rosenfeld" wrote:

> On Mon, 24 Jan 2005 09:49:02 -0800, "Michael"
> <[email protected]> wrote:
>
> >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
>
>
> 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
>

[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.

--
Don Guillett
SalesAid Software
[email protected]
"gennario" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
> "Ron Rosenfeld" wrote:
>
> > On Mon, 24 Jan 2005 09:49:02 -0800, "Michael"
> > <[email protected]> wrote:
> >
> > >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
> >
> >
> > 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
> >