Solved

Separate a Comma Delimited string into columns

Posted on 2011-09-12
6
314 Views
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

LastName-Smith
FirstName-Mary
MidName-Jane

Thank you
0
Comment
Question by:lbarnett419
6 Comments
 
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.

;-)
JeffCoachman
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 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
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 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
        Else
            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:

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
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:lbarnett419
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.
 ParseFirstNamewithNoMiddleName.txt
0
 
LVL 92

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)
0
 

Author Closing Comment

by:lbarnett419
ID: 36546859
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Newbie needs help printing from a form. 10 18
Sub Reports 8 21
Getting the Error "User-defined type not defined" in MS Access 2013 16 36
Importing and Dropping Table in Access 11 22
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

947 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now