Formula To Split Name

K

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
 
M

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.
 
M

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!
 
B

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
 
E

Eduardo

Hi In column C enter

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

in column D

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

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top