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:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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?
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
ABCD
1ann edwards sarahannedwardssarah
2yolanda m. darayolandam.dara
3herman lopezhermanlopez
4miike z. boromiikez.boro
5jen yannajenyanna
Sheet3
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,759
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top