Spliting Middle and Last Names Issue

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
AlKey, if you look at my table in post 11, it isn't working there either
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

AlKey

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

<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 />
 
Last edited:

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
So what does the final formula look like?

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

AlKey

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

bencar

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

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,363
Messages
5,486,412
Members
407,547
Latest member
Sankarasrinivas

This Week's Hot Topics

Top