Formula To Split Name

[Kiley]

I have a list of names in colum B, that I would like to split into column C
and D. The names in column B have a first name, middle intial and a last
name. I would like the last name to go into column c and the first name and
middle initial to go into column D.

Column B Column C Column D
Mary A Jones Jones Mary A

 

[Mike H]

Hi,

Put this in column C

=RIGHT(B1,LEN(B1)-FIND("*",SUBSTITUTE(B1," ","*",LEN(B1)-LEN(SUBSTITUTE(B1,"
","")))))

Then this in column D

=SUBSTITUTE(B1,C1,"")

Drag both down
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Ms-Exl-Learner]

Assume that you are having the data in A1 cell.

Copy the below formula and paste it in C1 cell.
=IF(A1="","",TRIM(MID(TRIM(A1),FIND("^",SUBSTITUTE(TRIM(A1),"
","^",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))),255)))

Copy the below formula and paste it in D1 cell.
=IF(A1="","",TRIM(LEFT(TRIM(A1),FIND("^",SUBSTITUTE(TRIM(A1),"
","^",(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))))))

Remember to Click Yes, if this post helps!

 

[Bernard Liengme]

To get "Jones": =MID(B1,FIND(" ",B1,FIND(" ",B1)+1)+1,LEN(B1))
to get "Mary A": =LEFT(B1,LEN(B1)-FIND(" ",B1)-1)
But these will not work if the middle initial is missing
best wishes

 

[Eduardo]

Hi In column C enter

=LEFT(A2,FIND(" ",A2,FIND(" ",A2)+1))

in column D

=RIGHT(A2,FIND(" ",A2))

 

[Jacob Skaria]

With name in cell A1
In cell B1
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

In cell C1
=LEFT(A1,LEN(A1)-LEN(B1))