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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
AlKey, if you look at my table in post 11, it isn't working there either

I think I know what the problem is. The formula should should enter two spaces if there are two words but I think the forum software compressed it to one. I made some changes to my last formula by adding REPT(" ",2) instead of " " . see post 20


Excel 2016 (Windows) 32 bit
ABCD
1ann edwards sarahannedwardssarah
2yolanda m. darayolandam.dara
3herman lopezhermanlopez
4miike z. boromiikez.boro
5jen yannajenyanna
Sheet3
 
Last edited:
Upvote 0
So what does the final formula look like?

(still wont improve the accuracy inherent in the overall process though)
 
Upvote 0
So what does the final formula look like?

(still wont improve the accuracy inherent in the overall process though)

the last formula in post #20
If you mean names like where the last two words are the last name? No. This can't be done.
Betty Jo Spickerman
Eddie Van Halen
 
Upvote 0
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))

Excellent!!! That works.. Thank you so much for your patience and diligence.

One last question though: how were you able to paste that small par tof the excel sheet on here?
 
Upvote 0
bencar, you seem now to have got to the stage of ignoring that, however good AlKey's formula is, it will NOT give you 100% accuracy where the middle term could be either the 2nd name or part of the last name
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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