ExcelBanter - Resorting full names

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Resorting full names (https://www.excelbanter.com/excel-worksheet-functions/69130-resorting-full-names.html)

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.

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.

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.