splitting firstname and last name separated by a column where one is potentially blank

[Mindbender12]

I have data that looks like this

Employee Name
Smith, Jim
Smith


,Jim
Smith, Jim - Worley

I am trying to create a field calculation that would return a first and last name like this

Last Name First Name
Smith Jim
Smith
Jim
Smith Jim - Worley

Trying things like Right([LineItem_query].[TheOwner],Len([LineItem_query].[TheOwner])-InStr([LineItem_query].[TheOwner],',')) but not getting all the values returning

Any help would be appreciated.

[CJ_London]

Looks like all you are doing is removing the comma so use the replace function. You might also want to Google the split function

[Mindbender12]

Looks like all you are doing is removing the comma so use the replace function. You might also want to Google the split function

I will look into the split function...thanks