Split Different Names with Special Characters

[wnicholl]

I have mutiple name listings that I'd like to only identify First and Last name.  The table is attached. 

Split Names

FIRSTNAME LASTNAME

FIRSTNAME LASTNAME 18

FIRSTNAME MIDDLENAME LASTNAME

FIRSTNAME LASTNAME - 18

FIRSTNAME MIDDLENAME LASTNAME CO

FIRSTNAME M. LASTNAME

FIRSTNAME LASTNAME -BR 18

FIRSTNAME M. LASTNAME CO

FIRSTNAME LASTNAME - BR 18

FIRSTNAME O'LASTNAME 66

FIRSTNAME LASTNAME TN

[wnicholl · Accepted solution]

I was able to come up with this workable solution. 

Staff 2 = LOOKUPVALUE('Table1'[Staff],'Table1'[ACTEXC],'Lawyers (Curr MTD Data) AWKQRP'[ACTEXC])