Change order of text in a cell?

A1=John Smith, I was able to find on the internet how to reverse it to =
Smith John. Question is if A1=John L Smith, then the formula changes it
to L Smith John. Is there a formula to make John L Smith change to
Smith John L? Is there a formula that can add the comma after the last
name also? Appreciate any help you can give me. I'm tiring out looking
for the answer on the internet, primarily because I having a hard time
typing in a good keyword in google cause I'm not sure how to describe
this problem (eg text manipulation, reversing order, switch text string,
etc). Thanks in advance.

Ed

=MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)+1,99)&", "&LEFT(A1,FIND(" ",A1,FIND("
",A1,1)+1))

Vaya con Dios,
Chuck, CABGx3



"abqhusker" <[email protected]> wrote in message
news:[email protected]...
> A1=John Smith, I was able to find on the internet how to reverse it to =
> Smith John. Question is if A1=John L Smith, then the formula changes it
> to L Smith John. Is there a formula to make John L Smith change to
> Smith John L? Is there a formula that can add the comma after the last
> name also? Appreciate any help you can give me. I'm tiring out looking
> for the answer on the internet, primarily because I having a hard time
> typing in a good keyword in google cause I'm not sure how to describe
> this problem (eg text manipulation, reversing order, switch text string,
> etc). Thanks in advance.
>
> Ed

Thanks for replying, Chuck. It works perfectly. I wish I could write
formulas like that. I have no idea what the MID, FIND, and LEFT
function work. Can you recommend a good resource for learning that kind
of stuff. In any event, thanks a million again.

Ed


CLR wrote:
> =MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)+1,99)&", "&LEFT(A1,FIND(" ",A1,FIND("
> ",A1,1)+1))
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "abqhusker" <[email protected]> wrote in message
> news:[email protected]...
>
>>A1=John Smith, I was able to find on the internet how to reverse it to =
>>Smith John. Question is if A1=John L Smith, then the formula changes it
>>to L Smith John. Is there a formula to make John L Smith change to
>>Smith John L? Is there a formula that can add the comma after the last
>>name also? Appreciate any help you can give me. I'm tiring out looking
>>for the answer on the internet, primarily because I having a hard time
>>typing in a good keyword in google cause I'm not sure how to describe
>>this problem (eg text manipulation, reversing order, switch text string,
>>etc). Thanks in advance.
>>
>>Ed
>
>
>

You're more than welcome Ed, and thanks for the nice note of appreciation.
As for learning the TEXT type formulas, the HELP file does have a basic,
albeit difficult to understand explanation.......the rest is a matter of
just playing with them back and forth until you get comfortable with them
and can get them to do what you want........and keep reading and searching
these newsgroups, and asking questions per your need.......they are the
greatest source of info on how to use Excel that I have found.........

Vaya con Dios,
Chuck, CABGx3





"abqhusker" <[email protected]> wrote in message
news:[email protected]...
> Thanks for replying, Chuck. It works perfectly. I wish I could write
> formulas like that. I have no idea what the MID, FIND, and LEFT
> function work. Can you recommend a good resource for learning that kind
> of stuff. In any event, thanks a million again.
>
> Ed
>
>
> CLR wrote:
> > =MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)+1,99)&", "&LEFT(A1,FIND("
",A1,FIND("
> > ",A1,1)+1))
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "abqhusker" <[email protected]> wrote in message
> > news:[email protected]...
> >
> >>A1=John Smith, I was able to find on the internet how to reverse it to =
> >>Smith John. Question is if A1=John L Smith, then the formula changes it
> >>to L Smith John. Is there a formula to make John L Smith change to
> >>Smith John L? Is there a formula that can add the comma after the last
> >>name also? Appreciate any help you can give me. I'm tiring out looking
> >>for the answer on the internet, primarily because I having a hard time
> >>typing in a good keyword in google cause I'm not sure how to describe
> >>this problem (eg text manipulation, reversing order, switch text string,
> >>etc). Thanks in advance.
> >>
> >>Ed
> >
> >
> >