| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Resorting full names
I need to resort names in a cell from John & Jane Doe to Doe, John & Jane.
The formulas discussed in previous threads work if there are just a first and last name in the cell, but not if there are several names as above. Does anyone have a formula that will work? Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Resorting full names
This formula will work, based on 2 assumptions. First, there are no tildes
(~) in any of your names. Second, the last name is always the last word following the last space. =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,1024)&", "&LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) Now, there are spaces in the formula, and I'm not sure what this will look like when it posts. Usually, line breaks will be inserted where the spaces are, so you may need to adjust accordingly. HTH, Elkar "Jim Sigurdson" wrote: I need to resort names in a cell from John & Jane Doe to Doe, John & Jane. The formulas discussed in previous threads work if there are just a first and last name in the cell, but not if there are several names as above. Does anyone have a formula that will work? Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Resorting full names
This works perfectly. A million thanks Elkar! JS
"Elkar" wrote: This formula will work, based on 2 assumptions. First, there are no tildes (~) in any of your names. Second, the last name is always the last word following the last space. =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,1024)&", "&LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) Now, there are spaces in the formula, and I'm not sure what this will look like when it posts. Usually, line breaks will be inserted where the spaces are, so you may need to adjust accordingly. HTH, Elkar "Jim Sigurdson" wrote: I need to resort names in a cell from John & Jane Doe to Doe, John & Jane. The formulas discussed in previous threads work if there are just a first and last name in the cell, but not if there are several names as above. Does anyone have a formula that will work? Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I find names in a workbook full of names | Excel Discussion (Misc queries) | |||
| How do i separate full names appearing in single cells? | Excel Worksheet Functions | |||
| Convert Local Names to Global Names | Excel Worksheet Functions | |||
| sorting names alphabetically when names may start with numbers | Excel Discussion (Misc queries) | |||
| Recurring Problems with Links with Full Path Names | Excel Discussion (Misc queries) | |||
Hybrid Mode
