splitting full name, into Lname, FNAME and MI 1

[jestrada101]

How can I split the following into separate columns?

COLUMN A
-----------
DOE, JOHN A
DOE, JANE

I'd like it like this

COLUMN B COLUMN C COLUMN D
-------- -------- ------
DOE JOHN A
DOE JANE


I've been using this for the firstname, but it keeps the last initial.
=RIGHT(A1,LEN(A1)-FIND(",",A1))

Thanks for any help.
JE

 

[xlbo]

Have a look at the MID() function

 

[jestrada101]

Thanks.. I found this online:

To return the last name of the full name in A2, use the following formula.

=LEFT(A2,IF(ISERROR(FIND(",",A2,1)),LEN(A2),FIND(",",A2,1)-1))

To return the first name of the full name in A2, use the following formula.

=TRIM(IF(ISERROR(FIND(",",A2,1)),A2,MID(A2,FIND(",",A2,1)+1,
IF(ISERROR(FIND(" ",A2,FIND(",",A2,1)+2)),LEN(A2),
FIND(" ",A2,FIND(",",A2,1)+2))-FIND(",",A2,1))))

To return the middle name of the full name in A2, use the following formula.

=TRIM(RIGHT(A2,LEN(A2)-IF(ISERROR(FIND(" ",A2,
FIND(" ",A2,FIND(",",A2,1)+2))),LEN(A2),
FIND(" ",A2,FIND(" ",A2,FIND(",",A2,1)+2))-1)))

 

[AnotherHiggins]

Have a look at Edit > Text to Columns.

Select comma and space as delimiters, and tick the box beside "Treat consecutive delimiters as one".

Much easier, IMHO.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.