Removing a name suffix (JR) and a middle name from a text box in Excel 2010
Posted on 2013-12-04
I am currently using the following formula to take names on a spreadsheet from Last, First to First Last:
=PROPER((RIGHT(A2,LEN(A2)-FIND(" ",A2,1)))&" "&(LEFT(A2,FIND(" ",A2,1)-2)))
Recently we've had a few names show up with Jr. or a middle name on the end appearing as such:
Doe JR, John or Doe Middle, John rather than just Doe, John
This causes my formula to go a little bananas. Rather than use ctrl+H and replace them, I would like to try and make some changes to the above formula to do it all in once pass if possible. How would I go about making the above formula ignore the third word as they appear in the examples?