|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
-LEN(SUBSTITUTE(A1," ","")))))
tootsuite[ at ]gmail.com wrote:
[Quoted Text] >
> 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
|
|
Thanks Dave - I have NO idea how this works, but it works
Dave Peterson wrote:
[Quoted Text] > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
> -LEN(SUBSTITUTE(A1," ","")))))
>
>
>
> tootsuite[ at ]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
|
|
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:
[Quoted Text] > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)
> -LEN(SUBSTITUTE(A1," ","")))))
>
>
>
> tootsuite[ at ]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
|
|
Actually, I figured it out. I just took the length of the last word,
then use LEFT function... thanks
tootsu...[ at ]gmail.com wrote:
[Quoted Text] > 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[ at ]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
|
|
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[ at ]gmail.com wrote:
[Quoted Text] >
> 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[ at ]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
|
|
BRILLIANT!! (Both of you)
Dave, could you please explain the use of "^^"?
"tootsuite[ at ]gmail.com" wrote:
[Quoted Text] > Actually, I figured it out. I just took the length of the last word,
> then use LEFT function... thanks
>
>
> tootsu...[ at ]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[ at ]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
>
>
|
|
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:
[Quoted Text] > BRILLIANT!! (Both of you)
>
> Dave, could you please explain the use of "^^"?
>
>
>
> "tootsuite[ at ]gmail.com" wrote:
>
> > Actually, I figured it out. I just took the length of the last word,
> > then use LEFT function... thanks
> >
> >
> > tootsu...[ at ]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[ at ]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
> >
> >
|
|
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[ at ]gmail.com wrote:
[Quoted Text] > 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[ at ]gmail.com" wrote:
> >
> > > Actually, I figured it out. I just took the length of the last word,
> > > then use LEFT function... thanks
> > >
> > >
> > > tootsu...[ at ]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[ at ]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
> > >
> > >
|
|
I get it now. Thanks again.
"Pete_UK" wrote:
[Quoted Text] > 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[ at ]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[ at ]gmail.com" wrote:
> > >
> > > > Actually, I figured it out. I just took the length of the last word,
> > > > then use LEFT function... thanks
> > > >
> > > >
> > > > tootsu...[ at ]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[ at ]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
> > > >
> > > >
>
>
|
|
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:
[Quoted Text] >
> 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[ at ]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[ at ]gmail.com" wrote:
> > >
> > > > Actually, I figured it out. I just took the length of the last word,
> > > > then use LEFT function... thanks
> > > >
> > > >
> > > > tootsu...[ at ]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[ at ]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
|
|
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[ at ]verizonXSPAM.net> wrote in message
news:451DAA61.CFBFBD35[ at ]verizonXSPAM.net...
[Quoted Text] >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[ at ]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[ at ]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
|
|
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:
[Quoted Text] > > 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[ at ]verizonXSPAM.net> wrote in message > news:451DAA61.CFBFBD35[ at ]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[ at ]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[ at ]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
|
|
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[ at ]verizonXSPAM.net> wrote in message
news:451E9CED.8710F273[ at ]verizonXSPAM.net...
[Quoted Text] > 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[ at ]verizonXSPAM.net> wrote in message > > news:451DAA61.CFBFBD35[ at ]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[ at ]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[ at ]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
|
|
I believe you.
I just don't remember it. (And I still say that I got it from Peo!)
Ragdyer wrote:
[Quoted Text] > > 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[ at ]verizonXSPAM.net> wrote in message > news:451E9CED.8710F273[ at ]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[ at ]verizonXSPAM.net> wrote in message > > > news:451DAA61.CFBFBD35[ at ]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[ at ]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[ at ]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
|
|
|