Format Names to mulitple fields First Middle Last

[Sam]

I need to split a field that has a name in it. Data appears as below

First Middle Last
First Last

I need either 1 field for "first and middle then 1 for last or split
in to 3.

Any help would be appreciated
Sam

 

[John Spencer]

This is always tricky. Why? Try these names as am example.

Madonna
Mary Anne Carter (Mary Anne is her first name)
Oscar de la Renta
Bobbi Jean
Arrington Anne Marie Hapsburg (Anne Marie is the middle name)
John Spencer, Jr

Not to mention typos that introduce extra spaces.
John P Spencer
John P Spencer
John P Spencer

It can be done and done with a fair degree of accuracy if your names follow
the pattern that there is always one or more spaces before the last name and
the last portion of text is the last name.

Assuming that you want to put the last name in one column and the remainder of
the name in a second column

Use this to get the last portion of the FullName

Trim( Mid([FullName],InstrRev(" " & [FullName]," ")))

Use this to get the rest
Trim(LEFT([FullName],InstrRev([FullName]," ")))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Sam]

This is always tricky.  Why? Try these names as am example.

Madonna
Mary Anne Carter (Mary Anne is her first name)
Oscar de la Renta
Bobbi Jean
Arrington Anne Marie Hapsburg (Anne Marie is the middle name)
John Spencer, Jr

Not to mention typos that introduce extra spaces.
John P   Spencer
John   P  Spencer
     John  P  Spencer

It can be done and done with a fair degree of accuracy if your names follow
the pattern that there is always one or more spaces before the last name and
the last portion of text is the last name.

Assuming that you want to put the last name in one column and the remainder of
the name in a second column

Use this to get the last portion of the FullName

   Trim( Mid([FullName],InstrRev(" " & [FullName]," ")))

Use this to get the rest
    Trim(LEFT([FullName],InstrRev([FullName]," ")))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County