Extract Surname from text

[nc]

Can you help me write a formula to extract the surname for a text.

For example,


Text

1. Peter V Crazy
2. James Van-Cleef
3. N N M B Chan

Results

1. Crazy
2. Van-Cleef
3. Chan

Thanks.

 

[Gary''s Student]

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,9999)

 

[Mike H]

Hi,

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

Mike