Split and combine name

Solved/Closed
tutrika - Feb 4, 2011 at 05:40 PM
 tutrika - Feb 8, 2011 at 12:59 PM
Hello,

i have 200K data that need to be fix.
let say:
a1: Dr. John G. James and Mel James
a2: John H. James
a3: James X. Brian and Timothy F. Call Ph D

I need to split Prev, first, middle, last name and suffix into separate column.
if there are 2 names, i have to combine the first name and middle name into first name column (ex. John G. and Mel) and last name on the last name column.
Please help.
thank you



Related:

3 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Feb 5, 2011 at 06:22 AM
formula in A2 will be
=LEFT(A1,SEARCH("and",A1)-1)
formula lin A3 will be
=RIGHT(A1,LEN(A1)-SEARCH("and",A1)-3)
you can copy the formula along the rows
0
Hi Venkat 1926,
thank you so much for the formula, i saw the 2 name got split into separate column
(James X. Brian and Timothy F. Brian <---> James X. Brian <----> Timothy F. Brian)
could you let me know if is possible to make all the first and middle initial into same column ?

Ex.:
A1 First name Last name
James X. Brian and Timothy F. Brian James X. and Timothy F. Brian

Thank you so very much for your help.
0
A1---------------------------------------------- First name------------------------ Last name
James X. Brian and Timothy F. <--> Brian James X. and Timothy F.<--> Brian
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Feb 5, 2011 at 10:13 PM
NOT CLEAR

they are not split. they are in the same column increase the width of A and see
0
Sorry for the confusion. :)

-----------A1-------------------------------------:----------B1-------------------------:------C1
James X. Brian and Timothy F. Brian--:-- James X. and Timothy F -- : --- Brian

column A1 need to split into column B1 (first name and initial only--> James X. and Timothy F.) and column C1(Last name only -->Brian)

Thanks for your help.
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Feb 6, 2011 at 08:16 PM
see this url
https://www.mrexcel.com/archive/general/finding-last-occurrence-of-character-in-string/


in C1 type this formula (yes first in C1)

=RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

in B1 type or copy paste this formula

=LEFT(A1,LEN(A1)-LEN(C1)-1)

you can copy the formulas down.
0
it works.
Thank you so much for your help.
0