Spliting Middle and Last Names Issue

bencar

Banned user
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:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

T. Valko

Well-known Member
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
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
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
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
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
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
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
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.

1,101,817
Messages
5,483,080
Members
407,378
Latest member
swanbrown

This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...