Access - Name Separation - with/out middle

I have a name field that may or may not have a middle name.

Please offer a formula to separate out the first from the last name.

For example


Will all result in:


Thank you.
exp vgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

There is no foolproof way.   Any name could have a prefix or a suffix.  So a complete procedure would keep tables of standard prefixes/suffixes, with and without punctuation and use those as part of the exclusion process.

A simple way is to use the Split() function to separate the name into pieces using space as the separator.  Then concatenate the first part, a space, and the last part.  This of course doesn't handle the prefix/suffix problem.  Nor does it handle the multi-word firstname/lastname problem.

And that is why names should always be stored separately as their parts.  It is very easy to put them back together for display but extremely difficult to separate them for processing.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
names are a PITA.

assuming you have:


YOu could use:
FirstName = Split(strName, " ")(0)
LastName = Split(strName, " ")(2)

But if you have:

strName = "JOHN DOE"

Then lastname won't work.  You could put that into a custom function, but even then, just taking the last segment as the last name might not work for names like:

John William Van Buren

But if you built your own function, you could include some logic, something like:

Public Function fnNamePart(FullName as string, Part as string) as string

    Dim arName() as String
    dim intParts as string

    arName = SPLIT(FullName, " ")
        Case "First"
            fnNamePart = arName(0)
        Case "Middle"
            if Ubound(arName) > 1 then
                fnNamePart = arName(1)
        Case "Last"
            if Ubound(arName) >= 3 Then
                fnNamePart = arName(2) & " " & arName(3)
                fnNamePart = arName(ubound(arName))
    End Select

End Function

Open in new window

I think you shouldn't ever assume that the last name is in slot 3.  I would assume it was always in the ubound slot and assume the first name is in slot1.  That works as long as there are no prefixes/suffixes and no multi-part first/last names.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

exp vgAuthor Commented:
I have tried this without success

FirstName:Split (([Patient First/Last], " ")(0))
exp vgAuthor Commented:
Also tried this without success:

LName: Left([Patient First/Last],InStr([Name]," "))
exp vgAuthor Commented:
Thank you.
Dale FyeOwner, Developing Solutions LLCCommented:

The point of testing for the UBound(arName) > 3 was to determine whether there were 4 or more parts to the name.  If so, then my assumption is that the last two are a multi-part last name.  This could, of course be a bad assumption, as some have two middle names (William Jim Bob Redneck), although in this case, Jim-Bob might be hyphenated.  

If it isn't a 4 part name, then the assumption is First, Middle, Last, although this could be incorrect as well (First, two part Last).  This is why I hate working with single field names!

@exp vg
You cannot use the Split function directly in a query.
What does "without success" mean?
Show us the input data.  Show us the output result.

You can only get the First Name with those two simple expressions.  You need to implement Dale's suggested code to get the last Name.  Or if you want something a little simpler that only takes one call:
Public Function FLName(FullName as string) as string

    Dim arName() as String
    Dim FIrstName as variant
    Dim LastName as Variant

              arName = SPLIT(FullName, " ")
              FirstName = arName(0)
              LastName = arName(ubound(arName))

             FLName = FirstName & " " & Lastname

End Function

Open in new window

Dale FyeOwner, Developing Solutions LLCCommented:
If you want to try using left, it would look like:

FName: Trim(Left([Patient First/Last],InStr([Patient First/Last]," ")))
LName: Trim(Right([Patient First/Last], instrrev([Patient First/Last], " ")))

but like Pat and I said, this will not work for multi-part last names.
exp vgAuthor Commented:
Thank you Dale - the first name worked, but the last name truncated or added extra characters - even when the name was not more than two parts

Please advise.
Try the function I wrote.

When working with public functions it is best to store them in standard modules so you can use them from anywhere.  So, you can use the function in code, in an expression, or in a query.
Dale FyeOwner, Developing Solutions LLCCommented:
Sorry,  That should have been:

LName: Trim(Mid([Patient First/Last], instrrev([Patient First/Last], " ")))

Using Mid rather than Right.  The InstrRev( ) function will give you the position number (from the left) of the first occurance of a character, working backwards from the right.  

InstrRev("John Doe", " ") would return 5.  And then the Right("John Doe", 5) would return "n Doe".  But the Mid( ) function starts as the character position provided and works to the right.
exp vgAuthor Commented:
Thank you Dale so much - is there a way for me to reward you points as well even though this question was closed. You have been offering a lot of help.
Dale FyeOwner, Developing Solutions LLCCommented:
don't worry about it.  just wanted to provide you with some additional options.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.