Spliting Middle and Last Names Issue

[bencar]

I wish to split the following names into separate cells. Entries with 1st, middle and last names are split normally into 3 separate columns. But entries with no middle names have an issue. Their last names are placed in the 2nd column as if it were their middle names. How can I fix it so that the last name of entries with no middle name go to the 3rd column (for last names) instead of the 2nd column which is reserved for middle names?

ann edwards sarah

yolanda m. dara

herman lopez

miike z. boro

jen yanna

<tbody>
</tbody>

 

[FDibbins]

Using AlKey's suggestion...

	A​	B​	C​	D​
1​	ann edwards sarah	ann	edwards	sarah
2​	yolanda m. dara	yolanda	m.	dara
3​	herman lopez	herman	lopez
4​	miike z. boro	miike	z.	boro
5​	jen yanna	jen	yanna
6​	Ron de Bruin	Ron	de	Bruin
7​	Eric von Lustbarden	Eric	von	Lustbarden
8​	John den Harver	John	den	Harver
9​	John Smith 3rd	John	Smith	3rd
10​	Mary Miller Snr	Mary	Miller	Snr

This shows my point even better

 

[FDibbins]

It would be easy to understand if someone could explain it simply.

That's what I attempted to do in post 8

 

[bencar]

Enter formula in B1 and pull it across to cell D1 and then down
=TRIM(MID(SUBSTITUTE(" "&IF(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))=1,SUBSTITUTE($A1," "," "),$A1)," ",REPT(" ",50)),50*COLUMNS($A:A),50))

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	ann edwards sarah	ann	edwards	sarah
2	yolanda m. dara	yolanda	m.	dara
3	herman lopez	herman		lopez
4	miike z. boro	miike	z.	boro
5	jen yanna	jen		yanna

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3
​

I did as you asked. But I still get 'Lopez' in the C column, not D column.

Also, how were you able to paste your excel sheet in your previous post?

 

[T. Valko]

But you got a delimiter as space in between the names. I'm sure theres a formula to split the names accordingly with that.

Yes, if the names contain just 2 words, a 1 word first name and a 1 word last name, then it's fairly simple to parse those names:

John Smith
Karen Kolter

However, when names have more than 2 words it gets complicated.

Betty Jo Spickerman
Eddie Van Halen

Betty Jo is the first name and Spickerman is the last name.

Eddie is the first name and Van Halen is the last name.

Both names are 3 words. So, what logic can we use to tell Excel that the first example has a 2 word first name but the second example has a 2 word last name?

We can't just use the number of space characters. They both have the same number of space characters but the space needed to split is not the same.

 

[AlKey]

I did as you asked. But I still get 'Lopez' in the C column, not D column.

Also, how were you able to paste your excel sheet in your previous post?

This is because there is a trailing spaces. Remove trailing space from herman lopez and the formula will readjust.

 

[bencar]

This is because there is a trailing spaces. Remove trailing space from herman lopez and the formula will readjust.

I dont understand. What do you mean by trailing space and where are they in 'herman lopez'? I checked in the cell and I dont see extra spaces before or after 'herman lopez'.

 

[AlKey]

I dont understand. What do you mean by trailing space and where are they in 'herman lopez'? I checked in the cell and I dont see extra spaces before or after 'herman lopez'.

I added trim function that will remove extra spaces
Try this
=TRIM(MID(SUBSTITUTE(" "&IF(LEN(TRIM($A1))-LEN(SUBSTITUTE(TRIM($A1)," ",""))=1,SUBSTITUTE(TRIM($A1)," "," "),TRIM($A1))," ",REPT(" ",50)),50*COLUMNS($A:A),50))

 

[bencar]

I added trim function that will remove extra spaces
Try this
=TRIM(MID(SUBSTITUTE(" "&IF(LEN(TRIM($A1))-LEN(SUBSTITUTE(TRIM($A1)," ",""))=1,SUBSTITUTE(TRIM($A1)," "," "),TRIM($A1))," ",REPT(" ",50)),50*COLUMNS($A:A),50))

I still get the same result. After I paste your formula and drag the cells across and down the result is still the same.

 

[FDibbins]

Regardless of whether the formula works, you are still left with the situation that it will never be 100% reliable/accurate, based on the comments made above

 

[AlKey]

All formulas worked find for me. This would be the last try.
=TRIM(MID(SUBSTITUTE(" "&IF(LEN(TRIM($A1))-LEN(SUBSTITUTE(TRIM($A1)," ",""))=1,SUBSTITUTE(TRIM($A1)," ",REPT(" ",2)),TRIM($A1))," ",REPT(" ",50)),50*COLUMNS($A:A),50))