Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 26
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,722
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,934
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 26
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How stop Excel file UK date order changing to US order in m.merge Roger Aldridge Excel Discussion (Misc queries) 1 October 9th 07 11:52 PM
changing text values to a different string sparks Excel Worksheet Functions 4 July 16th 07 07:01 PM
changing numbers in a text string in a new cell Xhawk57 Excel Discussion (Misc queries) 4 May 16th 07 06:22 PM
Changing text order quinner Excel Discussion (Misc queries) 2 February 15th 06 09:53 PM
Changing a specific character type in text string olasa Excel Discussion (Misc queries) 0 March 21st 05 12:35 AM


All times are GMT +1. The time now is 10:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"