Formula To Split Name
 

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

Formula To Split Name
 

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.


"Kiley" wrote:

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

Formula To Split Name
 

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("^",SUBSTITUT E(TRIM(A1),"
","^",(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))))))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------

"Kiley" wrote in message
...
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

Formula To Split Name
 

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Kiley" wrote in message
...
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

Formula To Split Name
 

Hi In column C enter

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

in column D

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

"Kiley" wrote:

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

Formula To Split Name
 

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

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

--
Jacob (MVP - Excel)


"Kiley" wrote:

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