Resorting full names

[Jim Sigurdson]

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.

[Elkar]

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.

[Jim Sigurdson]

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.