Help removing characters in a cell

[daydro]

So here is my dilemma and it is driving me nuts.

Starting Cell possibilities:
"FM. Porter Jr. INACT "
"FM. Porter Jr. GTD "
"FM. Porter Jr. OUT "

Desired Output:
"M. Porter Jr."

There is a space before and after "INACT"/"GTD"/"OUT". Some have "Jr." or "III" or nothing after the name. My though was to remove all text after the 2nd space from the right but I can't figure out how to count the spaces from the right.

Current Formula:
=IFERROR((LEFT(MID(AI10, 2, LEN(AI10)),SEARCH(" ",MID(AI10, 2, LEN(AI10)),SEARCH(" ",MID(AI10, 2, LEN(AI10)))+1)-1)),"")

Works flawless on names without a suffix but if they have a suffix it cuts it off.

 

[Scott Huish]

=MID(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),510)),""),2,255)

Excel 2010
	A	B
1	FM. Porter Jr. INACT	M. Porter Jr.
2	FM. Porter GTD	M. Porter
3	FM. Porter Jr. OUT	M. Porter Jr.
Sheet1

Cell Formulas
Range	Formula
B1	=MID(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),510)),""),2,255)
B2	=MID(SUBSTITUTE(A2,TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),510)),""),2,255)
B3	=MID(SUBSTITUTE(A3,TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",255)),510)),""),2,255)

 

[daydro]

You're awesome!

 

[Richard U]

Could you explain what this does?

=MID(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),510)),""),2,255)

Excel 2010
	A	B
1	FM. Porter Jr. INACT	M. Porter Jr.
2	FM. Porter GTD	M. Porter
3	FM. Porter Jr. OUT	M. Porter Jr.
Sheet1

Cell Formulas
Range	Formula
B1	=MID(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),510)),""),2,255)
B2	=MID(SUBSTITUTE(A2,TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),510)),""),2,255)
B3	=MID(SUBSTITUTE(A3,TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",255)),510)),""),2,255)

 

[Scott Huish]

Sure.
First it replaces all spaces with 255 spaces.
Then we take the right 510 characters (to get from the second space to the right from the original string - 255*2)
Since this of course has a lot of extra spaces in it, we TRIM it.
Now this will return the part we don't want of the string, so I substitute out of the original string.
Then return from the 2nd character to the end of the string.

 

[daydro]

You are a genius! I can't tell you how long I struggled with this. Now I am using "Substitute" everywhere!