Solved

Separate a Comma Delimited string into columns

Posted on 2011-09-12
6
333 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
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 …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

840 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