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



3 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
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
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
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
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
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