changing the order of text in a string
 

Using Excel 2003, I have a column of names, in the format
John Smith
Henry A. Jones
Maryanne George
Mary Ann George
I need to reverse the order, to be lastname, firstname +middle(if present)
Thus I get
Smith, John
Jones, Henry A.
etc.
How can I extract all the characters starting at the right side, to the
first space, and move that to the left, followed by a comma.

Pepper

changing the order of text in a string
 

This doesn't abbreviate middle names I'm afraid, but it does the job.

=RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" ",A2,FIND(" ",A2)+1)),FIND("
",A2),FIND(" ",A2,FIND(" ",A2)+1)))&", "&LEFT(A2,IF(ISERROR(FIND("
",A2,FIND(" ",A2)+1)),FIND(" ",A2),FIND(" ",A2,FIND(" ",A2)+1)))

It also will return an error if no name is in A2. But that would be easy to
fix, if necessary.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pepper" wrote:

Using Excel 2003, I have a column of names, in the format
John Smith
Henry A. Jones
Maryanne George
Mary Ann George
I need to reverse the order, to be lastname, firstname +middle(if present)
Thus I get
Smith, John
Jones, Henry A.
etc.
How can I extract all the characters starting at the right side, to the
first space, and move that to the left, followed by a comma.

Pepper

changing the order of text in a string
 

Here is a little bit shorter formula...

=MID(A2&", "&A2,FIND(TRIM(RIGHT(SUBSTITUTE(
TRIM(A2)," ",REPT(" ",99)),99)),A2),LEN(A2)+1)

--
Rick (MVP - Excel)


"Luke M" wrote in message
...
This doesn't abbreviate middle names I'm afraid, but it does the job.

=RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" ",A2,FIND(" ",A2)+1)),FIND("
",A2),FIND(" ",A2,FIND(" ",A2)+1)))&", "&LEFT(A2,IF(ISERROR(FIND("
",A2,FIND(" ",A2)+1)),FIND(" ",A2),FIND(" ",A2,FIND(" ",A2)+1)))

It also will return an error if no name is in A2. But that would be easy
to
fix, if necessary.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pepper" wrote:

Using Excel 2003, I have a column of names, in the format
John Smith
Henry A. Jones
Maryanne George
Mary Ann George
I need to reverse the order, to be lastname, firstname +middle(if
present)
Thus I get
Smith, John
Jones, Henry A.
etc.
How can I extract all the characters starting at the right side, to the
first space, and move that to the left, followed by a comma.

Pepper

changing the order of text in a string
 

Thank you guys.
I will go with the solution by Rick, because I can explain it to other people.

"Rick Rothstein" wrote:

Here is a little bit shorter formula...

=MID(A2&", "&A2,FIND(TRIM(RIGHT(SUBSTITUTE(
TRIM(A2)," ",REPT(" ",99)),99)),A2),LEN(A2)+1)

--
Rick (MVP - Excel)


"Luke M" wrote in message
...
This doesn't abbreviate middle names I'm afraid, but it does the job.

=RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" ",A2,FIND(" ",A2)+1)),FIND("
",A2),FIND(" ",A2,FIND(" ",A2)+1)))&", "&LEFT(A2,IF(ISERROR(FIND("
",A2,FIND(" ",A2)+1)),FIND(" ",A2),FIND(" ",A2,FIND(" ",A2)+1)))

It also will return an error if no name is in A2. But that would be easy
to
fix, if necessary.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pepper" wrote:

Using Excel 2003, I have a column of names, in the format
John Smith
Henry A. Jones
Maryanne George
Mary Ann George
I need to reverse the order, to be lastname, firstname +middle(if
present)
Thus I get
Smith, John
Jones, Henry A.
etc.
How can I extract all the characters starting at the right side, to the
first space, and move that to the left, followed by a comma.

Pepper