Mid after semi colon before space

Is there a formula that can be used to extract data after a semi-colon and before a space or no space? For instance all I want are the first names in a table such as below:

MCCOY;MICHAEL GENE I only want Michael
MAURATH;ANTON J. I only want Anton
SUNDINE;LAURA B. I only want Laura
TELLINGHUISEN;JANINE I only want Janine
MEYN;KENT A. I only want Kent

I know how to do Mid formulas, but I don't know how to do this when there are multiple variable because I don't know the number of characters after the semicolon or in Janine's case there isn't a space because there isn't a middle initial.

Thanks,

@Melvosh, you forgot to trap the FIND() error when there is no space

@tonym, see if this does what you need

Formula:

Please Login or Register  to view this content.

Originally Posted by Melvosh

I was rather shocked when it did seem to work

I know that feeling well, you think that you've got the perfect solution, then as soon as you post it, everyone sees your mistake(s)
Many times I've realised it as soon as I've submitted my reply, then had to do a very fast edit before anyine notices

@jason.b75: Your formula is incorrect if the string has a space before the first semi-colon.

Please Login or Register  to view this content.