Take word after last space before comma

[Mr Popodopolous]

I have a list where I want to put it to "Last Name, First Name" but if they have multiple words in their last name, I only want the last part of their last name to ensure that I won't have any errors when I'm performing a check as some reports don't put the full last name.

at the moment I have data like this:

van de Graaf, Generator O'the Morning, Topp

<tbody>
</tbody>

Flintstone, Wilma

I want to be able to have it so it will concatenate it to:

Graaf, Generator
Morning, Topp
Flintstone, Wilma

How can I do this, I'm thinking I have to do something where I have
FIND(",",A10)-FIND(",",SUBSTITUTE(A10," ",""))
which counts the number of spaces before the comma, but how do I use the information from that to get the number of characters after that final space

 

[Mr Popodopolous]

there should always be a space after the comma, barring some export corruption, which would probably render the formula useless anyway, so that's fine.

That last formula worked for all the example cases I threw at it

INPUT	Formula Output
name First, Last name	First, Last
Thornton, Ingrid	Thornton, Ingrid
van der Beek, James	Beek, James
Montoya, Inigo de	Montoya, Inigo

really long long last Name, This is what you call a crazy

Name, This

is it okay if I ask you to break that formula down? because I just look at that and from what I can work out,
TRIM(RIGHT(SUBSTITUTE(MID(A1,1,FIND(",",A1)-1) is taking the right of the character before the comma
," ",REPT(" ",1000)),1000))&", " is finding the space before that, and repeating the space 1000x and then taking 1000 characters before the character before the comma (and then trimming the spaces).
&TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(",",A1)+2,LEN(A1))," ",REPT(" ",1000)),1000)) is then adding a take LEFT of the first space after the comma after adding 1000x spaces where the first space is, and then taking the first 1000 characters after that (and again, trimming the spaces).
Is that a correct summary of the logic at play there? (I don't quite get how the LEN comes into it, although I guess it's got the starting character and is it just taking the length of the cell after that - I can't quite wrap my head around that part).

Thank you again, I never would have been able to work that out myself!

 

[RoryA]

Yes that's basically the logic. The LEN part is just there to make sure that everything after the comma and space is returned.