Separate a Comma Delimited string into columns

Posted on 2011-09-12
Medium Priority
Last Modified: 2012-05-12
Using a name like Smith, Mary Jane--I need to separate the name into 3 separate columns in MS Access Query Design page


Thank you
Question by:lbarnett419
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36526006
The kicker with things like this are things like:
Multiple spaces, one or more missing names, names in the wrong order, additional maiden/married names, Titles, etc.

In other words every name must follow the exact same structure, or it may not convert properly.

I am sure an expert will give you the syntax that will do what you are asking for directly.

LVL 86

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1200 total points
ID: 36526179
If all your names follow the same exact format as "Smith, Mary Jane", then you can use this:

FirstName: Split(Trim(Split("Smith, Mary Jane", ",")(1)), " ")(0)
LastName: Split("Smith, Mary Jane", ",")(1)
MiddleName: Split(Trim(Split("Smith, Mary Jane", ",")(1)), " ")(1)

As Jeff said, if the format is different you'll have to build code for each type
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 800 total points
ID: 36526566
Small correction to LSM's:

>>LastName: Split("Smith, Mary Jane", ",")(1)

should be:

LastName: Split("Smith, Mary Jane", ",")(0)

Of course, if this is being done in a query, you cannot use Split.  To expose Split in a query, you might like something like this:

Function SplitItem(Index As Long, InputStr As String, Optional Delimiter As String = " ", _
    Optional Limit As Long = -1, Optional CompareMode As VbCompareMethod = vbTextCompare)
    Dim arr As Variant
    Dim GetMember As Long
    SplitItem = ""
    If InputStr <> "" Then
        arr = Split(InputStr, Delimiter, Limit, CompareMode)
        If Index = 0 Then
            GetMember = UBound(arr)
        ElseIf Index > 0 Then
            GetMember = Index - 1
            GetMember = UBound(arr) + Index + 1
        End If
        If GetMember >= LBound(arr) And GetMember <= UBound(arr) Then
            SplitItem = arr(GetMember)
        End If
    End If
End Function

Open in new window

Or, you can review this article, which gives a fuller treatment of the topic of name parsing:

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.


Author Comment

ID: 36530433
While I appreciate all the help and the last name parses out beautifully, nothing seems to do the trick on parsing the first name when there is no middle name.

If the first name has a middle name in the same field, the parsing out of the first name is fine. However, when there is no middle name, I get an #Error.

I tested for an error using IsNull, IsEmpty and even Len=0 but I still get an #Error.
My formula is attached.
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36535083
If you use the SplitItem function that I outlined in http:#a36526566, then this gets your first name whether there is a middle name/initial or not:

SplitItem(1, Trim(SplitItem(2, TRightName, ",")), " ")

If you are using my GetNamePart from my article http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1819-Parsing-Names-in-MS-Office-Visual-Basic-6-and-Visual-Basic-for-Applications.html, then you can use this formula:

GetNamePart(TRightName, "first", True, False)

Author Closing Comment

ID: 36546859

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question