NEED TO FIND FIRST " " FROM RIGHT

Help - I have a column in excel that contains names, such as:

Joe Smith
Joe E. Smith
Joe Edward Smith, esq.
etc

I need to isolate the LAST WORD in the column, for example:

Smith
Smith
esq.

The only way I know how to do this is to search for the first " " - but
I need to start the search from the RIGHT, not the LEFT. As far as I
know, FIND function starts the search from the right... which isn't
what I want.

Can someone please tell me how to accomplish this task.

THANK YOU

0
tootsuite (5)
9/29/2006 10:35:09 PM
excel 39879 articles. 2 followers. Follow

14 Replies
306 Views

Similar Articles

[PageSpeed] 24

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
           -LEN(SUBSTITUTE(A1," ","")))))



tootsuite@gmail.com wrote:
> 
> Help - I have a column in excel that contains names, such as:
> 
> Joe Smith
> Joe E. Smith
> Joe Edward Smith, esq.
> etc
> 
> I need to isolate the LAST WORD in the column, for example:
> 
> Smith
> Smith
> esq.
> 
> The only way I know how to do this is to search for the first " " - but
> I need to start the search from the RIGHT, not the LEFT. As far as I
> know, FIND function starts the search from the right... which isn't
> what I want.
> 
> Can someone please tell me how to accomplish this task.
> 
> THANK YOU

-- 

Dave Peterson
0
petersod (12004)
9/29/2006 10:43:08 PM
Thanks Dave - I have NO idea how this works, but it works

Dave Peterson wrote:
> =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
>            -LEN(SUBSTITUTE(A1," ","")))))
>
>
>
> tootsuite@gmail.com wrote:
> >
> > Help - I have a column in excel that contains names, such as:
> >
> > Joe Smith
> > Joe E. Smith
> > Joe Edward Smith, esq.
> > etc
> >
> > I need to isolate the LAST WORD in the column, for example:
> >
> > Smith
> > Smith
> > esq.
> >
> > The only way I know how to do this is to search for the first " " - but
> > I need to start the search from the RIGHT, not the LEFT. As far as I
> > know, FIND function starts the search from the right... which isn't
> > what I want.
> >
> > Can someone please tell me how to accomplish this task.
> > 
> > THANK YOU
> 
> -- 
> 
> Dave Peterson

0
tootsuite (5)
9/29/2006 10:55:33 PM
Oops - I forgot to mention, I also need to isolate the "other" part of
the name:

Joe
Joe E.
Joe Edward Smith,

I can't really decipher your formula enough to extrapolate the reverse
of this.

Thanks


Dave Peterson wrote:
> =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
>            -LEN(SUBSTITUTE(A1," ","")))))
>
>
>
> tootsuite@gmail.com wrote:
> >
> > Help - I have a column in excel that contains names, such as:
> >
> > Joe Smith
> > Joe E. Smith
> > Joe Edward Smith, esq.
> > etc
> >
> > I need to isolate the LAST WORD in the column, for example:
> >
> > Smith
> > Smith
> > esq.
> >
> > The only way I know how to do this is to search for the first " " - but
> > I need to start the search from the RIGHT, not the LEFT. As far as I
> > know, FIND function starts the search from the right... which isn't
> > what I want.
> >
> > Can someone please tell me how to accomplish this task.
> > 
> > THANK YOU
> 
> -- 
> 
> Dave Peterson

0
tootsuite (5)
9/29/2006 11:05:44 PM
Actually, I figured it out. I just took the length of the last word,
then use LEFT function... thanks


tootsu...@gmail.com wrote:
> Oops - I forgot to mention, I also need to isolate the "other" part of
> the name:
>
> Joe
> Joe E.
> Joe Edward Smith,
>
> I can't really decipher your formula enough to extrapolate the reverse
> of this.
>
> Thanks
>
>
> Dave Peterson wrote:
> > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
> >            -LEN(SUBSTITUTE(A1," ","")))))
> >
> >
> >
> > tootsuite@gmail.com wrote:
> > >
> > > Help - I have a column in excel that contains names, such as:
> > >
> > > Joe Smith
> > > Joe E. Smith
> > > Joe Edward Smith, esq.
> > > etc
> > >
> > > I need to isolate the LAST WORD in the column, for example:
> > >
> > > Smith
> > > Smith
> > > esq.
> > >
> > > The only way I know how to do this is to search for the first " " - but
> > > I need to start the search from the RIGHT, not the LEFT. As far as I
> > > know, FIND function starts the search from the right... which isn't
> > > what I want.
> > >
> > > Can someone please tell me how to accomplish this task.
> > > 
> > > THANK YOU
> > 
> > -- 
> > 
> > Dave Peterson

0
tootsuite (5)
9/29/2006 11:10:56 PM
I stole it from Peo Sjoblom.  I'm not sure from whom he stole it <bg>.

but if you break it down into smaller pieces, it's not too bad.

Say you have "Joe Edward Smith, esq." in A1.

=substitute(a1," ","")
returns
JoeEdwardSmith,esq.
(all the spaces are gone)
=len(substitute(...)) will give 19

and len(a1) will give 22


Then
=SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
is the same as:
=SUBSTITUTE(A1," ","^^",22-19)
or
=SUBSTITUTE(A1," ","^^",3)
So this says to replace the 3rd space character with ^^.

So 
=SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
is essentially just doing:


This portion:
=FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
will return 18
since ^^ is the 18th (and 19th) character in that string: 
Joe Edward Smith,^^esq.
(go ahead and count, I did!)

So it's really just saying:
=right(a1,len(a1)-18)
or
=right(a1,22-18)
or
=right(a1,4)
which is 
esq.

Would I have come up with this?

Heck no!  But lots of people have and I've stolen their answers.




tootsuite@gmail.com wrote:
> 
> Thanks Dave - I have NO idea how this works, but it works
> 
> Dave Peterson wrote:
> > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
> >            -LEN(SUBSTITUTE(A1," ","")))))
> >
> >
> >
> > tootsuite@gmail.com wrote:
> > >
> > > Help - I have a column in excel that contains names, such as:
> > >
> > > Joe Smith
> > > Joe E. Smith
> > > Joe Edward Smith, esq.
> > > etc
> > >
> > > I need to isolate the LAST WORD in the column, for example:
> > >
> > > Smith
> > > Smith
> > > esq.
> > >
> > > The only way I know how to do this is to search for the first " " - but
> > > I need to start the search from the RIGHT, not the LEFT. As far as I
> > > know, FIND function starts the search from the right... which isn't
> > > what I want.
> > >
> > > Can someone please tell me how to accomplish this task.
> > >
> > > THANK YOU
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12004)
9/29/2006 11:21:05 PM
BRILLIANT!! (Both of you)

Dave, could you please explain the use of "^^"?



"tootsuite@gmail.com" wrote:

> Actually, I figured it out. I just took the length of the last word,
> then use LEFT function... thanks
> 
> 
> tootsu...@gmail.com wrote:
> > Oops - I forgot to mention, I also need to isolate the "other" part of
> > the name:
> >
> > Joe
> > Joe E.
> > Joe Edward Smith,
> >
> > I can't really decipher your formula enough to extrapolate the reverse
> > of this.
> >
> > Thanks
> >
> >
> > Dave Peterson wrote:
> > > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
> > >            -LEN(SUBSTITUTE(A1," ","")))))
> > >
> > >
> > >
> > > tootsuite@gmail.com wrote:
> > > >
> > > > Help - I have a column in excel that contains names, such as:
> > > >
> > > > Joe Smith
> > > > Joe E. Smith
> > > > Joe Edward Smith, esq.
> > > > etc
> > > >
> > > > I need to isolate the LAST WORD in the column, for example:
> > > >
> > > > Smith
> > > > Smith
> > > > esq.
> > > >
> > > > The only way I know how to do this is to search for the first " " - but
> > > > I need to start the search from the RIGHT, not the LEFT. As far as I
> > > > know, FIND function starts the search from the right... which isn't
> > > > what I want.
> > > >
> > > > Can someone please tell me how to accomplish this task.
> > > > 
> > > > THANK YOU
> > > 
> > > -- 
> > > 
> > > Dave Peterson
> 
> 
0
traveller (56)
9/29/2006 11:28:02 PM
I believe it is just a substitute character for the space - so that the
the last space, the one before the last word, is ^^, not " " - so as to
differentiate it from just a plain " "

me thinks anyways


Traveller wrote:
> BRILLIANT!! (Both of you)
>
> Dave, could you please explain the use of "^^"?
>
>
>
> "tootsuite@gmail.com" wrote:
>
> > Actually, I figured it out. I just took the length of the last word,
> > then use LEFT function... thanks
> >
> >
> > tootsu...@gmail.com wrote:
> > > Oops - I forgot to mention, I also need to isolate the "other" part of
> > > the name:
> > >
> > > Joe
> > > Joe E.
> > > Joe Edward Smith,
> > >
> > > I can't really decipher your formula enough to extrapolate the reverse
> > > of this.
> > >
> > > Thanks
> > >
> > >
> > > Dave Peterson wrote:
> > > > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
> > > >            -LEN(SUBSTITUTE(A1," ","")))))
> > > >
> > > >
> > > >
> > > > tootsuite@gmail.com wrote:
> > > > >
> > > > > Help - I have a column in excel that contains names, such as:
> > > > >
> > > > > Joe Smith
> > > > > Joe E. Smith
> > > > > Joe Edward Smith, esq.
> > > > > etc
> > > > >
> > > > > I need to isolate the LAST WORD in the column, for example:
> > > > >
> > > > > Smith
> > > > > Smith
> > > > > esq.
> > > > >
> > > > > The only way I know how to do this is to search for the first " " - but
> > > > > I need to start the search from the RIGHT, not the LEFT. As far as I
> > > > > know, FIND function starts the search from the right... which isn't
> > > > > what I want.
> > > > >
> > > > > Can someone please tell me how to accomplish this task.
> > > > >
> > > > > THANK YOU
> > > > 
> > > > -- 
> > > > 
> > > > Dave Peterson
> > 
> >

0
tootsuite (5)
9/30/2006 12:19:49 AM
Yeah, I think you are right - you are not likely to get that character
combination in any normal text. Dave often uses $$ in Find and Replace
operations, so I suppose if he hadn't "lifted" if from Peo, then you
might have found that combination of characters in the formula. <g>

Pete

tootsuite@gmail.com wrote:
> I believe it is just a substitute character for the space - so that the
> the last space, the one before the last word, is ^^, not " " - so as to
> differentiate it from just a plain " "
>
> me thinks anyways
>
>
> Traveller wrote:
> > BRILLIANT!! (Both of you)
> >
> > Dave, could you please explain the use of "^^"?
> >
> >
> >
> > "tootsuite@gmail.com" wrote:
> >
> > > Actually, I figured it out. I just took the length of the last word,
> > > then use LEFT function... thanks
> > >
> > >
> > > tootsu...@gmail.com wrote:
> > > > Oops - I forgot to mention, I also need to isolate the "other" part of
> > > > the name:
> > > >
> > > > Joe
> > > > Joe E.
> > > > Joe Edward Smith,
> > > >
> > > > I can't really decipher your formula enough to extrapolate the reverse
> > > > of this.
> > > >
> > > > Thanks
> > > >
> > > >
> > > > Dave Peterson wrote:
> > > > > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
> > > > >            -LEN(SUBSTITUTE(A1," ","")))))
> > > > >
> > > > >
> > > > >
> > > > > tootsuite@gmail.com wrote:
> > > > > >
> > > > > > Help - I have a column in excel that contains names, such as:
> > > > > >
> > > > > > Joe Smith
> > > > > > Joe E. Smith
> > > > > > Joe Edward Smith, esq.
> > > > > > etc
> > > > > >
> > > > > > I need to isolate the LAST WORD in the column, for example:
> > > > > >
> > > > > > Smith
> > > > > > Smith
> > > > > > esq.
> > > > > >
> > > > > > The only way I know how to do this is to search for the first " " - but
> > > > > > I need to start the search from the RIGHT, not the LEFT. As far as I
> > > > > > know, FIND function starts the search from the right... which isn't
> > > > > > what I want.
> > > > > >
> > > > > > Can someone please tell me how to accomplish this task.
> > > > > >
> > > > > > THANK YOU
> > > > > 
> > > > > -- 
> > > > > 
> > > > > Dave Peterson
> > > 
> > >

0
pashurst (2576)
9/30/2006 12:42:01 AM
I get it now. Thanks again.

"Pete_UK" wrote:

> Yeah, I think you are right - you are not likely to get that character
> combination in any normal text. Dave often uses $$ in Find and Replace
> operations, so I suppose if he hadn't "lifted" if from Peo, then you
> might have found that combination of characters in the formula. <g>
> 
> Pete
> 
> tootsuite@gmail.com wrote:
> > I believe it is just a substitute character for the space - so that the
> > the last space, the one before the last word, is ^^, not " " - so as to
> > differentiate it from just a plain " "
> >
> > me thinks anyways
> >
> >
> > Traveller wrote:
> > > BRILLIANT!! (Both of you)
> > >
> > > Dave, could you please explain the use of "^^"?
> > >
> > >
> > >
> > > "tootsuite@gmail.com" wrote:
> > >
> > > > Actually, I figured it out. I just took the length of the last word,
> > > > then use LEFT function... thanks
> > > >
> > > >
> > > > tootsu...@gmail.com wrote:
> > > > > Oops - I forgot to mention, I also need to isolate the "other" part of
> > > > > the name:
> > > > >
> > > > > Joe
> > > > > Joe E.
> > > > > Joe Edward Smith,
> > > > >
> > > > > I can't really decipher your formula enough to extrapolate the reverse
> > > > > of this.
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > > Dave Peterson wrote:
> > > > > > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
> > > > > >            -LEN(SUBSTITUTE(A1," ","")))))
> > > > > >
> > > > > >
> > > > > >
> > > > > > tootsuite@gmail.com wrote:
> > > > > > >
> > > > > > > Help - I have a column in excel that contains names, such as:
> > > > > > >
> > > > > > > Joe Smith
> > > > > > > Joe E. Smith
> > > > > > > Joe Edward Smith, esq.
> > > > > > > etc
> > > > > > >
> > > > > > > I need to isolate the LAST WORD in the column, for example:
> > > > > > >
> > > > > > > Smith
> > > > > > > Smith
> > > > > > > esq.
> > > > > > >
> > > > > > > The only way I know how to do this is to search for the first " " - but
> > > > > > > I need to start the search from the RIGHT, not the LEFT. As far as I
> > > > > > > know, FIND function starts the search from the right... which isn't
> > > > > > > what I want.
> > > > > > >
> > > > > > > Can someone please tell me how to accomplish this task.
> > > > > > >
> > > > > > > THANK YOU
> > > > > > 
> > > > > > -- 
> > > > > > 
> > > > > > Dave Peterson
> > > > 
> > > >
> 
> 
0
traveller (56)
9/30/2006 1:18:02 AM
Actually, I think I would have used char(1).

The ^^ could show up in a cell.  But I don't recall seeing anyone use char(1) in
any worksheet.

Pete_UK wrote:
> 
> Yeah, I think you are right - you are not likely to get that character
> combination in any normal text. Dave often uses $$ in Find and Replace
> operations, so I suppose if he hadn't "lifted" if from Peo, then you
> might have found that combination of characters in the formula. <g>
> 
> Pete
> 
> tootsuite@gmail.com wrote:
> > I believe it is just a substitute character for the space - so that the
> > the last space, the one before the last word, is ^^, not " " - so as to
> > differentiate it from just a plain " "
> >
> > me thinks anyways
> >
> >
> > Traveller wrote:
> > > BRILLIANT!! (Both of you)
> > >
> > > Dave, could you please explain the use of "^^"?
> > >
> > >
> > >
> > > "tootsuite@gmail.com" wrote:
> > >
> > > > Actually, I figured it out. I just took the length of the last word,
> > > > then use LEFT function... thanks
> > > >
> > > >
> > > > tootsu...@gmail.com wrote:
> > > > > Oops - I forgot to mention, I also need to isolate the "other" part of
> > > > > the name:
> > > > >
> > > > > Joe
> > > > > Joe E.
> > > > > Joe Edward Smith,
> > > > >
> > > > > I can't really decipher your formula enough to extrapolate the reverse
> > > > > of this.
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > > Dave Peterson wrote:
> > > > > > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
> > > > > >            -LEN(SUBSTITUTE(A1," ","")))))
> > > > > >
> > > > > >
> > > > > >
> > > > > > tootsuite@gmail.com wrote:
> > > > > > >
> > > > > > > Help - I have a column in excel that contains names, such as:
> > > > > > >
> > > > > > > Joe Smith
> > > > > > > Joe E. Smith
> > > > > > > Joe Edward Smith, esq.
> > > > > > > etc
> > > > > > >
> > > > > > > I need to isolate the LAST WORD in the column, for example:
> > > > > > >
> > > > > > > Smith
> > > > > > > Smith
> > > > > > > esq.
> > > > > > >
> > > > > > > The only way I know how to do this is to search for the first " " - but
> > > > > > > I need to start the search from the RIGHT, not the LEFT. As far as I
> > > > > > > know, FIND function starts the search from the right... which isn't
> > > > > > > what I want.
> > > > > > >
> > > > > > > Can someone please tell me how to accomplish this task.
> > > > > > >
> > > > > > > THANK YOU
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > >
> > > >

-- 

Dave Peterson
0
petersod (12004)
9/30/2006 1:26:21 AM
He either:
1) created it himself (likely)
2) stole it from me (also likely)
3) stole if from someone else (less likely)
--
Bob Umlas

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:451DAA61.CFBFBD35@verizonXSPAM.net...
>I stole it from Peo Sjoblom.  I'm not sure from whom he stole it <bg>.
>
> but if you break it down into smaller pieces, it's not too bad.
>
> Say you have "Joe Edward Smith, esq." in A1.
>
> =substitute(a1," ","")
> returns
> JoeEdwardSmith,esq.
> (all the spaces are gone)
> =len(substitute(...)) will give 19
>
> and len(a1) will give 22
>
>
> Then
> =SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
> is the same as:
> =SUBSTITUTE(A1," ","^^",22-19)
> or
> =SUBSTITUTE(A1," ","^^",3)
> So this says to replace the 3rd space character with ^^.
>
> So
> =SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
> is essentially just doing:
>
>
> This portion:
> =FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
> will return 18
> since ^^ is the 18th (and 19th) character in that string:
> Joe Edward Smith,^^esq.
> (go ahead and count, I did!)
>
> So it's really just saying:
> =right(a1,len(a1)-18)
> or
> =right(a1,22-18)
> or
> =right(a1,4)
> which is
> esq.
>
> Would I have come up with this?
>
> Heck no!  But lots of people have and I've stolen their answers.
>
>
>
>
> tootsuite@gmail.com wrote:
>>
>> Thanks Dave - I have NO idea how this works, but it works
>>
>> Dave Peterson wrote:
>> > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
>> >            -LEN(SUBSTITUTE(A1," ","")))))
>> >
>> >
>> >
>> > tootsuite@gmail.com wrote:
>> > >
>> > > Help - I have a column in excel that contains names, such as:
>> > >
>> > > Joe Smith
>> > > Joe E. Smith
>> > > Joe Edward Smith, esq.
>> > > etc
>> > >
>> > > I need to isolate the LAST WORD in the column, for example:
>> > >
>> > > Smith
>> > > Smith
>> > > esq.
>> > >
>> > > The only way I know how to do this is to search for the first " " - 
>> > > but
>> > > I need to start the search from the RIGHT, not the LEFT. As far as I
>> > > know, FIND function starts the search from the right... which isn't
>> > > what I want.
>> > >
>> > > Can someone please tell me how to accomplish this task.
>> > >
>> > > THANK YOU
>> >
>> > --
>> >
>> > Dave Peterson
>
> -- 
>
> Dave Peterson 


0
9/30/2006 4:27:35 PM
Could have come from John Walkenbach, too.

He has an explanation of how to build the formula:
http://www.j-walk.com/ss/excel/usertips/tip079.htm



Bob Umlas wrote:
> 
> He either:
> 1) created it himself (likely)
> 2) stole it from me (also likely)
> 3) stole if from someone else (less likely)
> --
> Bob Umlas
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:451DAA61.CFBFBD35@verizonXSPAM.net...
> >I stole it from Peo Sjoblom.  I'm not sure from whom he stole it <bg>.
> >
> > but if you break it down into smaller pieces, it's not too bad.
> >
> > Say you have "Joe Edward Smith, esq." in A1.
> >
> > =substitute(a1," ","")
> > returns
> > JoeEdwardSmith,esq.
> > (all the spaces are gone)
> > =len(substitute(...)) will give 19
> >
> > and len(a1) will give 22
> >
> >
> > Then
> > =SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
> > is the same as:
> > =SUBSTITUTE(A1," ","^^",22-19)
> > or
> > =SUBSTITUTE(A1," ","^^",3)
> > So this says to replace the 3rd space character with ^^.
> >
> > So
> > =SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
> > is essentially just doing:
> >
> >
> > This portion:
> > =FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
> > will return 18
> > since ^^ is the 18th (and 19th) character in that string:
> > Joe Edward Smith,^^esq.
> > (go ahead and count, I did!)
> >
> > So it's really just saying:
> > =right(a1,len(a1)-18)
> > or
> > =right(a1,22-18)
> > or
> > =right(a1,4)
> > which is
> > esq.
> >
> > Would I have come up with this?
> >
> > Heck no!  But lots of people have and I've stolen their answers.
> >
> >
> >
> >
> > tootsuite@gmail.com wrote:
> >>
> >> Thanks Dave - I have NO idea how this works, but it works
> >>
> >> Dave Peterson wrote:
> >> > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
> >> >            -LEN(SUBSTITUTE(A1," ","")))))
> >> >
> >> >
> >> >
> >> > tootsuite@gmail.com wrote:
> >> > >
> >> > > Help - I have a column in excel that contains names, such as:
> >> > >
> >> > > Joe Smith
> >> > > Joe E. Smith
> >> > > Joe Edward Smith, esq.
> >> > > etc
> >> > >
> >> > > I need to isolate the LAST WORD in the column, for example:
> >> > >
> >> > > Smith
> >> > > Smith
> >> > > esq.
> >> > >
> >> > > The only way I know how to do this is to search for the first " " -
> >> > > but
> >> > > I need to start the search from the RIGHT, not the LEFT. As far as I
> >> > > know, FIND function starts the search from the right... which isn't
> >> > > what I want.
> >> > >
> >> > > Can someone please tell me how to accomplish this task.
> >> > >
> >> > > THANK YOU
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12004)
9/30/2006 4:35:57 PM
It seems no one wants to believe me when I say that it came from Leo Heuser,
way back in 2001!

http://tinyurl.com/g2ca7

http://tinyurl.com/k9lzb


-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:451E9CED.8710F273@verizonXSPAM.net...
> Could have come from John Walkenbach, too.
>
> He has an explanation of how to build the formula:
> http://www.j-walk.com/ss/excel/usertips/tip079.htm
>
>
>
> Bob Umlas wrote:
> >
> > He either:
> > 1) created it himself (likely)
> > 2) stole it from me (also likely)
> > 3) stole if from someone else (less likely)
> > --
> > Bob Umlas
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:451DAA61.CFBFBD35@verizonXSPAM.net...
> > >I stole it from Peo Sjoblom.  I'm not sure from whom he stole it <bg>.
> > >
> > > but if you break it down into smaller pieces, it's not too bad.
> > >
> > > Say you have "Joe Edward Smith, esq." in A1.
> > >
> > > =substitute(a1," ","")
> > > returns
> > > JoeEdwardSmith,esq.
> > > (all the spaces are gone)
> > > =len(substitute(...)) will give 19
> > >
> > > and len(a1) will give 22
> > >
> > >
> > > Then
> > > =SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
> > > is the same as:
> > > =SUBSTITUTE(A1," ","^^",22-19)
> > > or
> > > =SUBSTITUTE(A1," ","^^",3)
> > > So this says to replace the 3rd space character with ^^.
> > >
> > > So
> > > =SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
> > > is essentially just doing:
> > >
> > >
> > > This portion:
> > > =FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
> > > will return 18
> > > since ^^ is the 18th (and 19th) character in that string:
> > > Joe Edward Smith,^^esq.
> > > (go ahead and count, I did!)
> > >
> > > So it's really just saying:
> > > =right(a1,len(a1)-18)
> > > or
> > > =right(a1,22-18)
> > > or
> > > =right(a1,4)
> > > which is
> > > esq.
> > >
> > > Would I have come up with this?
> > >
> > > Heck no!  But lots of people have and I've stolen their answers.
> > >
> > >
> > >
> > >
> > > tootsuite@gmail.com wrote:
> > >>
> > >> Thanks Dave - I have NO idea how this works, but it works
> > >>
> > >> Dave Peterson wrote:
> > >> > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
> > >> >            -LEN(SUBSTITUTE(A1," ","")))))
> > >> >
> > >> >
> > >> >
> > >> > tootsuite@gmail.com wrote:
> > >> > >
> > >> > > Help - I have a column in excel that contains names, such as:
> > >> > >
> > >> > > Joe Smith
> > >> > > Joe E. Smith
> > >> > > Joe Edward Smith, esq.
> > >> > > etc
> > >> > >
> > >> > > I need to isolate the LAST WORD in the column, for example:
> > >> > >
> > >> > > Smith
> > >> > > Smith
> > >> > > esq.
> > >> > >
> > >> > > The only way I know how to do this is to search for the first "
" -
> > >> > > but
> > >> > > I need to start the search from the RIGHT, not the LEFT. As far
as I
> > >> > > know, FIND function starts the search from the right... which
isn't
> > >> > > what I want.
> > >> > >
> > >> > > Can someone please tell me how to accomplish this task.
> > >> > >
> > >> > > THANK YOU
> > >> >
> > >> > --
> > >> >
> > >> > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
>
> -- 
>
> Dave Peterson

0
ragdyer1 (4060)
9/30/2006 6:58:49 PM
I believe you. 

I just don't remember it.  (And I still say that I got it from Peo!)

Ragdyer wrote:
> 
> It seems no one wants to believe me when I say that it came from Leo Heuser,
> way back in 2001!
> 
> http://tinyurl.com/g2ca7
> 
> http://tinyurl.com/k9lzb
> 
> --
> Regards,
> 
> RD
> 
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:451E9CED.8710F273@verizonXSPAM.net...
> > Could have come from John Walkenbach, too.
> >
> > He has an explanation of how to build the formula:
> > http://www.j-walk.com/ss/excel/usertips/tip079.htm
> >
> >
> >
> > Bob Umlas wrote:
> > >
> > > He either:
> > > 1) created it himself (likely)
> > > 2) stole it from me (also likely)
> > > 3) stole if from someone else (less likely)
> > > --
> > > Bob Umlas
> > >
> > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > news:451DAA61.CFBFBD35@verizonXSPAM.net...
> > > >I stole it from Peo Sjoblom.  I'm not sure from whom he stole it <bg>.
> > > >
> > > > but if you break it down into smaller pieces, it's not too bad.
> > > >
> > > > Say you have "Joe Edward Smith, esq." in A1.
> > > >
> > > > =substitute(a1," ","")
> > > > returns
> > > > JoeEdwardSmith,esq.
> > > > (all the spaces are gone)
> > > > =len(substitute(...)) will give 19
> > > >
> > > > and len(a1) will give 22
> > > >
> > > >
> > > > Then
> > > > =SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
> > > > is the same as:
> > > > =SUBSTITUTE(A1," ","^^",22-19)
> > > > or
> > > > =SUBSTITUTE(A1," ","^^",3)
> > > > So this says to replace the 3rd space character with ^^.
> > > >
> > > > So
> > > > =SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
> > > > is essentially just doing:
> > > >
> > > >
> > > > This portion:
> > > > =FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
> > > > will return 18
> > > > since ^^ is the 18th (and 19th) character in that string:
> > > > Joe Edward Smith,^^esq.
> > > > (go ahead and count, I did!)
> > > >
> > > > So it's really just saying:
> > > > =right(a1,len(a1)-18)
> > > > or
> > > > =right(a1,22-18)
> > > > or
> > > > =right(a1,4)
> > > > which is
> > > > esq.
> > > >
> > > > Would I have come up with this?
> > > >
> > > > Heck no!  But lots of people have and I've stolen their answers.
> > > >
> > > >
> > > >
> > > >
> > > > tootsuite@gmail.com wrote:
> > > >>
> > > >> Thanks Dave - I have NO idea how this works, but it works
> > > >>
> > > >> Dave Peterson wrote:
> > > >> > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
> > > >> >            -LEN(SUBSTITUTE(A1," ","")))))
> > > >> >
> > > >> >
> > > >> >
> > > >> > tootsuite@gmail.com wrote:
> > > >> > >
> > > >> > > Help - I have a column in excel that contains names, such as:
> > > >> > >
> > > >> > > Joe Smith
> > > >> > > Joe E. Smith
> > > >> > > Joe Edward Smith, esq.
> > > >> > > etc
> > > >> > >
> > > >> > > I need to isolate the LAST WORD in the column, for example:
> > > >> > >
> > > >> > > Smith
> > > >> > > Smith
> > > >> > > esq.
> > > >> > >
> > > >> > > The only way I know how to do this is to search for the first "
> " -
> > > >> > > but
> > > >> > > I need to start the search from the RIGHT, not the LEFT. As far
> as I
> > > >> > > know, FIND function starts the search from the right... which
> isn't
> > > >> > > what I want.
> > > >> > >
> > > >> > > Can someone please tell me how to accomplish this task.
> > > >> > >
> > > >> > > THANK YOU
> > > >> >
> > > >> > --
> > > >> >
> > > >> > Dave Peterson
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12004)
9/30/2006 7:37:54 PM
Reply:

Similar Artilces:

Remove "Subfilter" Icon Needed
Greetings. I am using MS Excel 2007 (beta), and it's a peach, to say the least. I especially like the improved filtering functions. There is one thing, however, that I hope you will include in the final version scheduled to come out next year. When I filter the contents of a particular cell (right mouse click, filter by selected cell's value, etc.), and then in a different column filter on the contents of another cell within this first filter, I get another smaller "subfilter," of course. After doing something to the contents in this subfilter (filling in a blank, ...

Need help grouping charts
I have identical charts which occur on multiple sheets. Is there any wa to group them for editing? When I group the sheets themselves, it seems to deactivate the charts I tried doing it with a macro as an alternative and then flipping th sheets one by one and keying the macro, but I get a message box with: "Run-time error 1004", "Unable to get the ChartObjects property of th worksheet class". Does anyone have any ideas -- Izz ----------------------------------------------------------------------- Izzy's Profile: http://www.excelforum.com/member.php?action=getin...

Reports and making it look prettier: Last Name, First Name Rank
I'm trying to pretty up my report by eliminating the forced space created by having one field of the report for 'LastName', one for 'FirstName', and one for 'Rank.' The Rank isn't too huge of an issue, and if three items in one field gets to be too much, I have no problem leaving that as a side item of sorts. But, I want my report to look a bit better by putting the names together! I want the report to go to my table, pull the LastName from that column, and pair it with the matching FirstName in the column to the right. (Since it's just...

DB Mail is is not working. Need help please.
I have try stop and start the db mail by executing the SPs below but no luck. Does anyone has seem the error below and how to fix it. Thanks in advance for your advise/help. --steps USE msdb; go EXECute sysmail_stop_sp; go EXECute sysmail_start_sp; go EXECute sysmail_help_queue_sp @queue_type = 'Mail'; go queue_type length state last_empty_rowset_time last_activated_time ---------- ----------- ---------------------------------------------------------------- ----------------------- ------------...

Need to convert point on screen to various screen resolutions
Let's say you click on a button on your screen at 1000,2500 TWIPS and your resolution is 800 X 600. Now you change your screen resolution to 1024 X 768 and you need to click on the same button in it's new location on the screen. Who's 100 times smarter than I am and can do some tricky math that will tell me the TWIPS to find that button? I'd ned to do the same calculation for other screen resolutions like 640 X 480 etc. http://www.applecore99.com/api/api012.asp -- Regards, Tom Ogilvy "Donna YaWanna" <diy@mdahospital.com> wrote in message news:%23HMLEWW2...

How do I find a value on a line?
I made a line graph of data to use as a calibration. I know the y value and I want to find the X value. Is there a way that I find find this specifically on the line without using a trendline formula or guessing by looking at the gridlines? The only way to find a specific value is to use the formula. Rgs, Bou If you can accept piece-wise linear interpolation, see Interactive Chart http://www.tushar- mehta.com/excel/software/interactive_chart_display/index.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity s...

How do I activate the trial exel 2003 program if it does't find c.
My Browser works good but the activation window does't find conection You need to talk to your ISP. Connection problems are in their domain. I have the free trial version. I have used it freely for a month. My SBC/Yahoo DSL connected fine, however at one time I was using Earthlink dial-up. That connection service was terrible. I live in a small town close to Detroit, and had to connect to the closest major city to go on-line. If you don't get good service, try another ISP. "Dolores Dueck" wrote: > My Browser works good but the activation window does't find c...

Help need for .net assembly in Workflow manager
A simple code (given below) creates a task thro' .net assembly . I invoked this code via console application & found that it is running fine. It is creating task in CRM successfully. The problem is : when I included it in Work flow manager, it displays that "task completed" in workflow monitor but it is not creating a task in CRM . using System; using System.IO; using TaskCreation.CRMWeb; namespace TaskCreation { /// <summary> /// Summary description for Class1. /// </summary> public class Class1 { public Class1() { // // TODO: Add construc...

Need Help
Query: Say i have multiple sheets dated 04/01/10 to 04/30/10, each contain names, say A, B, C, D, etc... but these name does not appear in the same order in each sheet. Now data is in time format in front of A, B, C, D, etc. Eg. in sheet 04/01/10 A1 B1 A 1:00:00 D 1:53:00 C 2:45:00 like this data in other sheet till 04/30/10. Now in a new sheet i want this data to be collated in time format in front of the individual sorted in Alphabetical order. Also it should give me the Sum of the data. Is it possible and how? Thanks in advance for the help!...

corrupted PST file
Hi, I have Outlook 98, running on XP, on a PII 400Mghz Dell desktop. My PST file is about 1.95GB. It has been running extremely slow, and has had to be restarted a bunch of times in the past 2 weeks. Yesterday, it gave an error that the PST file was corrupted, and that PST file need to have the Inbox Repair Tool run on the file, and then the system restarted. I ran the Inbox Repair Tool on the PST, and it did not work. I am totally unable to use the file, which has all my critical contacts and emails. If someone could help out, I would be MOST grateful.... I pulling my friggin ...

Conditional Formatting
Hi I need some help with conditional formatting. I'm trying to highlight those cells which have a plus sign in them. Can someone help with the formula to enter into the conditional formatting dialogue? Grateful for any assistance. Best Wishes Hi Colin if the Plus sign + is part of a text string Select the column range first then =FIND("+",A2:$A$160) change the range to your needs. If the + sign is alone in the cell, you don't need a formula, just select > Cell value is > then select Equal to> and type the plus sign in the third box. H...

Do you need MS Access to query on an Access table?
I'm asking these questions because I have looked at a lot of stuff in the discussion groups and still confused. I am fairly good at Excel programming but haven't been able to get Excel and Access to talk. First, I have Office 2003 Professional at work and Office 2003 without Access at home. I want to work on developing Excel programming at home which will get data from Access to Excel with either MS Query or with programming. Is it possible to just have the .mdb files on the home computer for Excel to work with, or do I need the Access program too? The info I want ...

Reverse Find and Replace ?
Hi, I'm wanting to Reverse find and replace a particular character in a Powershell string. e.g. reverse find "b" in "abcabc" and replace with "d" to give "abcadc". Could someone please advise how this could be achieved ? Thanks, Patrick Sorry - also should have stated that just the first instance of a particular character should be replaced. On Jan 8, 11:23=A0am, Patrick <prynh...@gmail.com> wrote: > Hi, > > I'm wanting to Reverse find and replace a particular character in a > Powershell string. =A0e.g. r...

bug in Excel 2003; need to install Excel 2002
I have Office 2003, and I found a bug in Excel 2003. The linear regression results using Excel 2003 differs with that of Excel 2002 for some cases. How do I can uninstall Excel 2003 (and install Excel 2002) without affecting the other Office apps (like Word 2003 and Powerpt2003)? Hi Excel 2003 has solved some bugs/errors with statsitical functions. So I would guess the 'new' result are more correct than the old ones -- Regards Frank Kabel Frankfurt, Germany "Speqter" <Speqter@discussions.microsoft.com> schrieb im Newsbeitrag news:451EE055-7023-4C3B-B816-829BA9FC6E...

finding a result
Is there something in Excel that you can compare contents of one Excel file with another file and output the differences? Myrna Larson and Bill Manville have developed a compare that's very nice. http://www.cpearson.com/excel/whatsnew.htm look for compare.xla But the bad news is that this does a cell-by-cell comparison. A1 compares to A1, x99 to x99, etc. If you insert/delete a row or column, then this won't work very well. Sam wrote: > > Is there something in Excel that you can compare contents > of one Excel file with another file and output the > differences? -...

Contact "Look for" won't bring up contact
Hi, we seem to have a groups of contacts that can only be located through an "Advanced Find" but not through the "Look for" finder. When they do come up in "Advanced Find" they're not editable. What could cause this? Thanks, Ken KPR, When you do a Quick Find (Look For) search in CRM, only Active CRM Records (records that have a Status of "Active") will be displayed in the Search Results view. When you search for records in the Advanced Find it will return all CRM Records (with a Status of Active and Inactive). It appears that all of the con...

Unable to find FrxClient.cfg file, when applying FRx service pack.
When I try to apply a services apck for my FRx client i got this error " Unable to find FrxClient.cfg file. Setup cannot continue if this file is corrupted or not found. Setup will abort now." Please help! ...

Need help in generating a report.
I have a spreadsheet (Sheet1) that summarizes all of my contracts. Column A contains the name of my clients for whom I do several jobs for. Column B contains the name of the contract. Column C contains a flag to indicate the job has been completed. Column F contains the profit/loss amount for that job. I only have a handful of clients but several contracts with each. On Sheet 2, I would like to have a report that sorts all of my contracts by clients, along with the name, amount each one made or lost, and then have a total for each client. The report should look something similar to this.....

NEED HELP/SQL UPGRADE/PLEASE!
Tried to upgrade to 2.0 last night, and am running into a major difficulty getting SO Admin to talk to SQL Server 2005. My error message is "SQL Server does not exist or access denied. Connection open (Connect())" I have been on the phone with Microsoft Tech Support for most of the day (and we have been running the store without a computer, which is no mean feat). Have installed and removed SQL 2005 several times, checked to make sure everything is running and started, that everything that is supposed to be is enabled.....any insights out there? By the way, it would be a goo...

Macro help needed #3
I'm learning macros at the moment and thought I'd venture to write one which I needed myself but I could do with some help, since I spent hours today trying everything that occured to me and searching the internet when I should be studying since I've an exam on macros tomorrow. I import an list of people from the internet by using a web query in excel (labelled provisions) I selected a group using the first letter of the last name and used record macro to copy the selection and paste the values into another worksheet labelled with that letter, eg labelled A. At the end of the m...

Need help with Prepaid deferrals
Our client wants to expense out the prepaid amount over the year. Just like the Revenue/Deferral module does; however the module post everything to the GL so all 13 transaction get posted (the original check and the 12 deferral transactions). They need a way to stop that and behave more like a recurring batch. At the same time the want to be able to see the accout. The account has to have the period setup, the amount apportioned, the last posted transaction date, and the remaining balance left in the account. If anybody can help, please answer. We have tried so many things but n...

Need help with a date function formula
I have an excel sheet header which includes a date, the date is t represent yesterday's date ( =Today()-1 ), which then would come int the spread sheet as August 8th, 2005, assuming today was August 9th. The problem is on days like Monday when the previous day was sunday how can I make the formula output the Friday before the Monday instea of Sunday ...example on Monday, August 11th, the formula would read August 10th, I need it to read August 8th...? Please help, thank -- KA ----------------------------------------------------------------------- KAA's Profile: http://www.excelforum...

Need Help Calling New Instance of MembershipUser Within Custom User Class
Hi. I am getting myself mixed up trying to tie together ASPNET 2.0's MembershipUser and ProfileBase in addition to come custom fields in the same User class. I am close, but am having a hard time accessing the MembershipUser methods - I geek getting "object reference not set to an instance of an object" or errors of that ilk. Could somebody please show me how to tie in MembershipUser into my User class? I am also open to putting both ProfileBase and MembershipUser into an interface but would appreciate if somebody could provide a code example of how to do this i...

extract data from middle / right of string
I have a column in a spreadsheet containing the following type of string: 330189149967 Ideal - Series 2 (DVD) 3 12.99 GBP 38.97 GBP I would like to extract the number after the (DVD) in this string. However, the set of characters (DVD) does not always appear in the string. The only thing that is always constant is the number of spaces before the 3 on the right of the string. So I think the best way to extract the number is count 4 spaces from the right and return the number after the 4th space. This number could also be more than 1 character which might make things difficult. Any ideas? Tha...

New to Access, need some help with a query
I need some help. I have basic Access experience and cant, for the life of me figure out what im doing wrong. Currently I have two tables: Table 1 with Fields: Category ID Sub Cat ID Product Term ID Product Name Description Table 2 with Fields: Field1 Field2 Field3 Field4 Field5 Field6 Field7 What I’ve been trying to do is create a query that will allow me to show a datasheet with specific changes to data and only certain fields. I need to see a datasheet like this: Field2 (from Table 2) Category ID (from Table 1) Field4 (from Table 2) Field5 (from Table 2) Field6 (from Table 2) Field1 ...