Bob Phillips gave the answers below for splitting names from cells:

=LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)

and

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))

Using these formulas on this example John A Doe results in John A and
Doe, is it possible to split it to show John / A / Doe in 3 separate
cells, I know I could use the formulas again on the John A result to
split them but I'd like to do it in 1 go

If possible could anyone explain what the formula is doing, I
understand LEFT and LEN etc but don't understand the use of ^^

Thanks

Paul


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=391603

Paul, the first space is replaced with ^^ to find the second space, the
item up to the second space is then known.


Paul Sheppard Wrote:
Bob Phillips gave the answers below for splitting names from cells:

=LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)

and

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))

Using these formulas on this example John A Doe results in John A and
Doe, is it possible to split it to show John / A / Doe in 3 separate
cells, I know I could use the formulas again on the John A result to
split them but I'd like to do it in 1 go

If possible could anyone explain what the formula is doing, I
understand LEFT and LEN etc but don't understand the use of ^^

Thanks

Paul


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=391603