Respect to this answer: replace spaces as nulls in a string and subtract from the original string length to find out how many spaces there are, then make the last one char(22). Find the char(22) and you have the start point for your mid function.
I’m using this to find the last name for a list of people who have lots of first names (Christmas list, if you must know).
=MID(A1,FIND(CHAR(22),SUBSTITUTE(A1,”
“,CHAR(22),LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))+1,255)