# Thread: Spliting Middle and Last Names Issue Thanks: 0 Likes: 0

1. ## Re: Spliting Middle and Last Names Issue

AlKey, if you look at my table in post 11, it isn't working there either

2. ## Re: Spliting Middle and Last Names Issue

Originally Posted by FDibbins
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

3. ## Re: Spliting Middle and Last Names Issue

So what does the final formula look like?

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

4. ## Re: Spliting Middle and Last Names Issue

Originally Posted by FDibbins
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

5. ## Re: Spliting Middle and Last Names Issue

Originally Posted by AlKey
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?

6. ## Re: Spliting Middle and Last Names Issue

Originally Posted by bencar
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?

7. ## Re: Spliting Middle and Last Names Issue

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

8. ## Re: Spliting Middle and Last Names Issue

Tagged for future reference...

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•