Oops! I was depending on the results shown on the spreadsheet, not the ones in your post.

Now I see that the formulas you show in your spreadsheet are different from the ones in your post. Personally, I'd appreciate it if you took another look at what you've given us and make it consistent so that we can play nice.

Now regarding Deborah Harris, my guess is that there are two trailing spaces following her last name in the live data that are invisible, as spaces tend to be. Applying your code to this data would result in extracting a last name that looks like rris^^ (where ^ represents a space). This is why in all cases where leading or trailing spaces could present a problem, the data should be "trimmed"; i.e., those spaces should be removed from the data before applying any logic.