• Status: Solved
• Priority: Medium
• Security: Public
• Views: 334

# Removing a name suffix (JR) and a middle name from a text box in Excel 2010

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?

Thanks!

-Straws
0
StrawsPulledAtRandom
1 Solution

Commented:
What results do you expect in those two cases?

If you just want John Doe try this formula

=PROPER(MID(A2&" "&A2,FIND(",",A2)+2,LEN(A2)-IFERROR(FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2),1)))

regards, barry
0

EngineerCommented:
Keeping all three you might try

=PROPER((RIGHT(A2,LEN(A2)-FIND(", ",A2,1)-1))&" "&(LEFT(A2,FIND(", ",A2,1)-1)))
0

Author Commented:
This did the trick as requested.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.