Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How stop Excel file UK date order changing to US order in m.merge | Excel Discussion (Misc queries) | |||
changing text values to a different string | Excel Worksheet Functions | |||
changing numbers in a text string in a new cell | Excel Discussion (Misc queries) | |||
Changing text order | Excel Discussion (Misc queries) | |||
Changing a specific character type in text string | Excel Discussion (Misc queries) |