Can't deal with these middle names.

[D_Boyd]

I have a lot of list with names (1000 plus name on each list).
Some people have middle names.
I need to seperate each name
Given Name with any middle name and surname.
So two cells from the one

I know how to use text to colume (Delimited using spaces)
But when I am done, I have to spend hours adding the middle name (if they have one) to the "given Name cell.

Any ideas? ..... HELP!............. LOL
D_Boyd

 

[Peter_SSs]

Welcome to the MrExcel board!

In future, please consider the following for small sample data that is easy for helpers to test with,
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Here are two options, depending on whether you have the recent TEXT... functions or not.

22 08 27.xlsm
	A	B	C	D	E	F
1
2	Tom Jones	Tom	Jones		Tom	Jones
3	Ann Jane Smith	Ann Jane	Smith		Ann Jane	Smith
Names

Cell Formulas
Range	Formula
B2:B3	B2	=TEXTBEFORE(A2," ",-1)
C2:C3	C2	=TEXTAFTER(A2," ",-1)
E2:E3	E2	=LEFT(A2,LEN(A2)-LEN(F2)-1)
F2:F3	F2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

 

[Phuoc]

Try

Book1
	A	B	C
1
2	Aaa Ccc	Aaa	Ccc
3	Aaa Bbb Ccc	Aaa Bbb	Ccc
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=LEFT(A2,LEN(A2)-LEN(C2)-1)
C2:C3	C2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

Hahaha... same idea.

 

[Phuoc]

Also, you can use Flash Fill.

 

[D_Boyd]

That worked good except, it makes errors (sometime).
On surname. It put surname in the same cell as givenname...sometimes.

See below

D_Boyd

PS: I don't know "Flash Fill"

test2.xlsx
	A	B	C
1	Anna Maria Schulmeister	Anna Maria	Schulmeister
2	Joseph Preis	Joseph	Preis
3	Nicolaus Bollak	Nicolaus	Bollak
4	Bartolomei Duckart	Bartolomei	Duckart
5	Barbara Beier	Barbara	Beier
6	Susanne Seewald	Susanne	Seewald
7	Anna Sophia Schmidt	Anna Sophia	Schmidt
8	Anna Maria Devivje	Anna Maria	Devivje
9	Maria Maeyer	Maria	Maeyer
10	Joseph Vogel	Joseph	Vogel
11	Franz Distel	Franz	Distel
12	Johann Peter Vogel	Johann Peter	Vogel
13	Nicolaus Seewald	Nicolaus	Seewald
14	Anton Schlitter	Anton	Schlitter
15	Margarethe Bohn	Margarethe	Bohn
16	Barbara Rollhäuser	Barbara	Rollhäuser
17	Philipp Durban	Philipp	Durban
18	Franz Strömel	Franz	Strömel
19	Joseph Strawinski	Joseph	Strawinski
20	Johann Peter Devivje	Johann Peter	Devivje
21	Georg Rollhäuser	Georg Rollhäuser
22	Joseph Meyer	Joseph Meyer
23	Johannes Dietz	Johannes	Dietz
24	Franz Hartmann	Franz	Hartmann
25	Jacob Engraf	Jacob	Engraf
26	Michael Streitenberger	Michael	Streitenberger
27	Peter Spötter	Peter	Spötter
28	Maria Bender	Maria	Bender
29	Anna Maria Kayser	Anna Maria	Kayser
30	Catharina Engraf	Catharina	Engraf
31	Magdalene Weingardt	Magdalene	Weingardt
32	Catharina Meyer	Catharina	Meyer
33	Christine Strömel	Christine	Strömel
34	Matthias Rollhäuser	Matthias	Rollhäuser
35	Catharina Schibelbein	Catharina	Schibelbein
36	Maria Theresa Altmayer	Maria Theresa	Altmayer
37	Maria Happel	Maria	Happel
38	Joseph Gerling	Joseph	Gerling
39	Eva Suchsdorf	Eva Suchsdorf
Sheet1

Cell Formulas
Range	Formula
B1:B39	B1	=LEFT(A1,LEN(A1)-LEN(C1)-1)
C1:C39	C1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

 

[Peter_SSs]

Those lines where it did not work have several spaces after the last name in column A. Try this formula in column C instead.

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",100)),100))

 

[D_Boyd]

That worked perfectly!
I want to thank you for your help. Will save me many hours.

Thank you again!

D_Boyd

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Peter_SSs]

BTW, thanks for the sample data with XL2BB. It also gave you the reason/answer quickly whereas with only an image we would never have known those extra spaces were there.