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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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,019
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.
 

Forum statistics

Threads
1,089,339
Messages
5,407,687
Members
403,158
Latest member
Limerick2030

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top