Spliting Middle and Last Names Issue

bencar

Banned user
Joined
Jun 8, 2016
Messages
149
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>
 
Last edited:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
This is practically impossible to achieve 100% accuracy.

Consider these names:

Betty Jo Spickerman
Eddie Van Halen

Jo and Van are not middle names!

How do you tell Excel that Betty Jo is the first name and Van Halen is the last name?
 

bencar

Banned user
Joined
Jun 8, 2016
Messages
149
This is practically impossible to achieve 100% accuracy.

Consider these names:

Betty Jo Spickerman
Eddie Van Halen

Jo and Van are not middle names!

How do you tell Excel that Betty Jo is the first name and Van Halen is the last name?

But isnt there a formula for that?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,147
Office Version
365, 2010
Platform
Windows, Mobile
But isnt there a formula for that?
As Biff states for the examples given, how do you think that Excel would know which is part of the first name and which is part of the Surname unless you had a delimiter like a comma to separate the 2 or a separate list telling it for each name?
 

bencar

Banned user
Joined
Jun 8, 2016
Messages
149
As Biff states for the examples given, how do you think that Excel would know which is part of the first name and which is part of the Surname unless you had a delimiter like a comma to separate the 2 or a separate list telling it for each name?
Hold on, I'm sure with the mountain of difficult stuff you can pull off on excel theres got to be a way for something as seemingly simple as this. I refuse to believe that. But you got a delimiter as space in between the names. I'm sure theres a formula to split the names accordingly with that.
 
Last edited:

AlKey

Active Member
Joined
Oct 15, 2013
Messages
395
Unfortunately, there is no formula or VBA code that would be able to differentiate between the middle name the last name that consists of two words. I think this should be easy to understand.
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Yes, you can split on the space, that is not the problem.

As Tony (Biff) said, the problem comes in with excel being able to identify if the 2nd term is actually the 2nd name, or part of the last name
Ron de Bruin
Eric von Lustbarden
John den Harver

are all examples where the 2nd term is actually part of the last name. We can see that, but how would excel know?

On the flip side...
John Smith 3rd
Mary Miller Snr

also have 3 terms, but here, the 3rd term is not the last name

Robert Mac Arthur
would Mac be the 2nd name or part of the last? (I know people for both ways)

To excel, words are just random characters that don't really have any "meaning", so how would it be expected - in a logical, mathematical - manner, to deal with these?
 

AlKey

Active Member
Joined
Oct 15, 2013
Messages
395
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))

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ann edwards sarah</td><td style="font-weight: bold;color: #002060;;">ann</td><td style="font-weight: bold;color: #002060;;">edwards</td><td style="font-weight: bold;color: #002060;;">sarah</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">yolanda m. dara</td><td style="font-weight: bold;color: #002060;;">yolanda</td><td style="font-weight: bold;color: #002060;;">m.</td><td style="font-weight: bold;color: #002060;;">dara</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">herman lopez</td><td style="font-weight: bold;color: #002060;;">herman</td><td style="font-weight: bold;color: #002060;;"></td><td style="font-weight: bold;color: #002060;;">lopez</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">miike z. boro</td><td style="font-weight: bold;color: #002060;;">miike</td><td style="font-weight: bold;color: #002060;;">z.</td><td style="font-weight: bold;color: #002060;;">boro</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">jen yanna</td><td style="font-weight: bold;color: #002060;;">jen</td><td style="font-weight: bold;color: #002060;;"></td><td style="font-weight: bold;color: #002060;;">yanna</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet3</p><br /><br />
 

bencar

Banned user
Joined
Jun 8, 2016
Messages
149
Unfortunately, there is no formula or VBA code that would be able to differentiate between the middle name the last name that consists of two words. I think this should be easy to understand.
It would be easy to understand if someone could explain it simply.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,544
Messages
5,415,163
Members
403,569
Latest member
ekslit

This Week's Hot Topics

Top