# Spliting Middle and Last Names Issue

#### FDibbins

##### Well-known Member
AlKey, if you look at my table in post 11, it isn't working there either

### 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
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
So what does the final formula look like?

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

#### AlKey

##### Active Member
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
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?

#### AlKey

##### Active Member
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
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

#### itr674

##### Well-known Member
Tagged for future reference...