Formula To Split Name

  • Thread starter Thread starter Kiley
  • Start date Start date
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
 
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.
 
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!
 
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
 
Hi In column C enter

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

in column D

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

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