Solved

Separate a Comma Delimited string into columns

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Outlook Free & Paid Tools
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: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

803 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