?
Solved

Separate a Comma Delimited string into columns

Posted on 2011-09-12
6
Medium Priority
?
350 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 85

Accepted Solution

by:
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
0
 
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
        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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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

Author Closing Comment

by:lbarnett419
ID: 36546859
Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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 viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

765 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