How can I separate the first and last name of a cell?

If cell A1 contains [Jones,Mike], how can I get it to separate but still use t开发者_运维知识库he first name as text in the other cell? Yes, text to columns would work greatly, but it does let me use the first name in a formula.

With name in A1, for surname:

=LEFT(A1,FIND(",",A1,1)-1)

For first name(s):

=RIGHT(A1,FIND(", ",A1,1)-2)

Ex: John Doe

First name:

=LEFT(A1,FIND(" ",A1,1)-1)

Calculate first name length from left by Finding the " " (Space) character position in the cell string, -1 to remove the space in the counting.

Surname:

=RIGHT(A1,(LEN(A1)-FIND(" ",A1,1)))

Calculate Surname string length by first finding the value where surname starts. Full cell length minus first name + the space.

Ex: Doe,John

Surname:

=LEFT(A1,FIND(",",A1,1)-2)

First name:

 =RIGHT(A1,(LEN(A1)-FIND(",",A1,1)))