Just to throw out another option, one trick that I stumbled across a while ago, and which is often helpful when extracting something from the start or end of a string, is to replace an existing separator (like a space) with a massive collection of spaces using REPT. Then I can hack off that piece with a single LEFT or RIGHT using a fixed length matching the size of that space block, and wrap that in TRIM to remove the extra spaces.
For example, if one space becomes 15 spaces, I can safely hack off 15 characters and know that I won’t get any part of the adjacent text. The number depends on the maximum expected size of the piece I want. In this case, it’s probably safe to assume that someone’s surname won’t be longer than 15 characters. So far been a very reliable technique, and often results in a shorter formula than one that relies on finding things in the text.
SUBSTITUTE(Name, " " & TRIM(RIGHT(SUBSTITUTE(Name, " ", REPT(" ", 15)), 15)), "")