I am trying to compare a list of names between two sheets. Unfortunately the names are not formatted the same. One sheet has a first name column and a last name column. The second sheet has a full name that includes a middle initial and/or one or more middle names. I am trying to come up with a formula that would return the start of a text string until it hits the space and then another that would return the remainder after hitting the last space. Text to columns doesn't work too well since it is a different number of names for different people. Here is an example of what I have and what I would like it to return. Any help would be appreciated.

Sample data: John James William Doe
Formula 1 result: John
Formula 2 result: Doe