On Sat, 23 Feb 2008 13:23:58 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Assuming all your "double names" are structured as shown (with the comma and
the word "and"), give these two formulas (which assume your names are in A1)
a try...

B1: =MID(A1,FIND(",",A1)+2,IF(ISERROR(FIND(" and ",A1)),LEN(A1)+1,FIND("
and ",A1))-FIND(",",A1)-2)&" "&LEFT(A1,FIND(",",A1)-1)

C1: =IF(ISERROR(FIND(" and ",A1)),"",MID(A1,FIND(" and ",A1)+4,99)&"
"&LEFT(A1,FIND(",",A1)-1))

You can then copy these down as required. By the way, these formulas should
work whether there is a second name attached with the word "and" in Column A
or not.

Rick

It does give an error if there's no comma. But ...

A suggestion, Rick:

Your formulas are multi-line and break on the <space after the <". I have
found that if I break these long formulas in the formula bar, at an appropriate
length, using <alt-enter, I can then paste them into my newsreader and they
can be copy/pasted directly back into a formula bar, and work with no further
editing.
--ron