software

I need to resort names in a cell from John amp; Jane Doe to Doe, John amp; 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.

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(quot;~quot;,SUBSTITUTE(A1,quot; quot;,quot;~quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot;
quot;,quot;quot;)))) 1,1024)amp;quot;, quot;amp;LEFT(A1,FIND(quot;~quot;,SUBSTITUTE(A1,quot;
quot;,quot;~quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot; quot;,quot;quot;))))-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

quot;Jim Sigurdsonquot; wrote:

gt; I need to resort names in a cell from John amp; Jane Doe to Doe, John amp; Jane.
gt;
gt; The formulas discussed in previous threads work if there are just a first
gt; and last name in the cell, but not if there are several names as above. Does
gt; anyone have a formula that will work? Thanks.

This works perfectly. A million thanks Elkar! JS

quot;Elkarquot; wrote:

gt; This formula will work, based on 2 assumptions. First, there are no tildes
gt; (~) in any of your names. Second, the last name is always the last word
gt; following the last space.
gt;
gt; =MID(A1,FIND(quot;~quot;,SUBSTITUTE(A1,quot; quot;,quot;~quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot;
gt; quot;,quot;quot;)))) 1,1024)amp;quot;, quot;amp;LEFT(A1,FIND(quot;~quot;,SUBSTITUTE(A1,quot;
gt; quot;,quot;~quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot; quot;,quot;quot;))))-1)
gt;
gt; Now, there are spaces in the formula, and I'm not sure what this will look
gt; like when it posts. Usually, line breaks will be inserted where the spaces
gt; are, so you may need to adjust accordingly.
gt;
gt; HTH,
gt; Elkar
gt;
gt; quot;Jim Sigurdsonquot; wrote:
gt;
gt; gt; I need to resort names in a cell from John amp; Jane Doe to Doe, John amp; Jane.
gt; gt;
gt; gt; The formulas discussed in previous threads work if there are just a first
gt; gt; and last name in the cell, but not if there are several names as above. Does
gt; gt; anyone have a formula that will work? Thanks.