B1 is a name or names (e.g. Bill Smith and Richard Jones).

C1:c500 is a list of last names (eg. Smith, Stone, Jones, etc)

So if B1 was Bill Smith and Richard Jones I would like a coulumn giving
Smith and another one givimg Jones.

Trying to avoid macros if possible.

Cheers,
Jai