• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

Name separation in imported data

I could use some suggestions on how to separate names that are lumped together in one field in an imported data set.  

The name field can include one of the three formats
1. Mark White & Jean Sanchez
2. Harold & Bernadette Katz
3. Mary Jane Kennedy


What I would like to do is to have a query that can convert the single field into potentially 4 new fields that I will add to my table.  The desired result is shown in the screenshot.

LAST_NAME
FIRST_NAME
LAST_NAME2
FIRST_NAME2

Is it possible to write an update query that could do this with all of the potential variation?

DATA2.JPG
0
snyperj
Asked:
snyperj
  • 7
  • 7
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try this codes

Function SplitInformation(ByVal idx As Integer, ByVal sInformation As String) As String

    Dim infArr() As String
   
    SplitInformation = ""
    If sInformation & "" = "" Then SplitInformation = "": Exit Function
    
    If InStr(sInformation, "&") > 0 Then
        infArr = Split(sInformation, "&")
        If UBound(Split(sInformation, " ")) > 3 Then
        Select Case idx
            Case 1
                SplitInformation = Split(Trim(infArr(0)), " ")(0)
            Case 2
                SplitInformation = Split(Trim(infArr(0)), " ")(1)
            Case 3
                SplitInformation = Split(Trim(infArr(1)), " ")(0)
            Case 4
                SplitInformation = Split(Trim(infArr(1)), " ")(1)
            
        End Select
        Else
        Select Case idx
            Case 1
                SplitInformation = Split(Trim(infArr(0)), " ")(0)
            Case 2
                SplitInformation = Split(Trim(infArr(1)), " ")(1)
            Case 3
                SplitInformation = Split(Trim(infArr(1)), " ")(0)
            Case 4
                SplitInformation = Split(Trim(infArr(1)), " ")(1)
            
        End Select
        End If
    Else
        infArr = Split(sInformation, " ")
        Select Case idx
            Case 1
                SplitInformation = infArr(0)
            Case 2
                If UBound(infArr) > 1 Then
                    SplitInformation = infArr(1) & " " & infArr(2)
                    Else
                    SplitInformation = infArr(1)
                End If
            Case else
                msgbox "Check Query"

        End Select
    End If
End Function

Open in new window


to use the function in a query, place each of this in a column of the query grid

Last_Name:SplitInformation(2, [Name])
First_Name:SplitInformation(1, [Name])
Last_Name2:SplitInformation(4, [Name])
First_Name2:SplitInformation(3, [Name])




0
 
snyperjAuthor Commented:
I keep getting an endless "check query" message box.
0
 
Rey Obrero (Capricorn1)Commented:
change this

            Case else
                msgbox "Check Query"
with

            Case else
               SplitInformation=""
               exit function
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
snyperjAuthor Commented:
It tries and tries... but seems to choke with the following errors....
1.JPG
2.JPG
0
 
Rey Obrero (Capricorn1)Commented:
that only means that you have other names formatted differently from the three formats you posted above
0
 
snyperjAuthor Commented:
Groan, ok I see that , yes occasionally a period is put in after an initial etc.  

Can you make it so it just skips, or ignores, anything outside the three given formats?  I do not think it will ever be many.  We are going to get this list monthly.

0
 
Rey Obrero (Capricorn1)Commented:
post sample data
0
 
snyperjAuthor Commented:
Actually, it isn't because of that.  The ones that are causing fields in the query to display #Error are those that have names like:

┬áRichard & Ruth Ann Day
 James & Anne Marie Simpson

0
 
Rey Obrero (Capricorn1)Commented:
so, how do you want those names parsed?
0
 
snyperjAuthor Commented:
Richard would be firstname and Day would be lastname

Ruth Ann would be firstname2 & Day would be lastname2
0
 
als315Commented:
What if you will have names like:
Gabriel Garcia & Penelope Cruz Sanchez
0
 
snyperjAuthor Commented:
LASTNAME=GARCIA
FIRSTNAME=GABRIEL
LASTNAME2=SANCHEZ
FIRSTNAME=PENELOPE CRUZ

It won't be perfect, but if we have to hand edit some it will still be better than it is now.
0
 
als315Commented:
I took name Gabriel Garcia Marquez :).
and mean
LASTNAME=SANCHEZ
FIRSTNAME=GABRIEL GARCIA
LASTNAME2=SANCHEZ
FIRSTNAME2=PENELOPE CRUZ
0
 
Rey Obrero (Capricorn1)Commented:
try this codes, copy and paste
Function SplitInformation(ByVal idx As Integer, ByVal sInformation As String) As String

    Dim infArr() As String, iArr1() As String
   
    SplitInformation = ""
    If sInformation & "" = "" Then SplitInformation = "": Exit Function
    
    If InStr(sInformation, "&") > 0 Then
        infArr = Split(sInformation, "&")
        iArr1 = Split(Trim(infArr(1)), " ")
        
        Select Case UBound(Split(Trim(infArr(0)), " "))
            Case 1         'firstname and lastname
            Select Case UBound(Split(Trim(infArr(1)), " "))
                Case 0
                SplitInformation = "Check Names"
                Case 1
                
                SplitInformation = "Check Names"
                Select Case idx
                    Case 1
                        SplitInformation = Split(Trim(infArr(0)), " ")(0)
                    Case 2
                        SplitInformation = Split(Trim(infArr(0)), " ")(1)
                    Case 3
                        SplitInformation = Split(Trim(infArr(1)), " ")(0)
                    Case 4
                        SplitInformation = Split(Trim(infArr(1)), " ")(1)
                    
                End Select
                
                Case 2
                Select Case idx
                    Case 1
                        SplitInformation = Split(Trim(infArr(0)), " ")(0)
                    Case 2
                        SplitInformation = Split(Trim(infArr(0)), " ")(1)
                    Case 3
                        SplitInformation = Split(Trim(infArr(1)), " ")(0) & " " & Split(Trim(infArr(1)), " ")(1)
                    Case 4
                        SplitInformation = Split(Trim(infArr(1)), " ")(2)
                    
                End Select
            
            End Select
            Case 0         'firstname
            Select Case UBound(Split(Trim(infArr(1)), " "))
                Case 0
                SplitInformation = "Check Names"
                Case 1
                
                Select Case idx
                    Case 1
                        SplitInformation = Trim(infArr(0))
                    Case 2
                        SplitInformation = Split(Trim(infArr(1)), " ")(1)
                    Case 3
                        SplitInformation = Split(Trim(infArr(1)), " ")(0)
                    Case 4
                        SplitInformation = Split(Trim(infArr(1)), " ")(1)
                    
                End Select

                Case 2
                Select Case idx
                    Case 1
                        SplitInformation = Trim(infArr(0))
                    Case 2
                        SplitInformation = Split(Trim(infArr(1)), " ")(2)
                    Case 3
                        SplitInformation = Split(Trim(infArr(1)), " ")(0) & " " & Split(Trim(infArr(1)), " ")(1)
                    Case 4
                        SplitInformation = Split(Trim(infArr(1)), " ")(2)
                    
                End Select
            
            End Select
           
        
        End Select
    Else
        infArr = Split(sInformation, " ")
        Select Case idx
            Case 1
                If UBound(infArr) > 1 Then

                SplitInformation = infArr(0) & " " & infArr(1)
                Else
                SplitInformation = infArr(0)
                End If
            Case 2
                If UBound(infArr) > 1 Then
                    SplitInformation = infArr(2)
                    Else
                    SplitInformation = infArr(1)
                End If
            Case Else
                    SplitInformation = ""

        End Select
    End If
End Function

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
try this sample db, run query1
DB-Q-27031669-NameParse.mdb
0
 
snyperjAuthor Commented:
This works!  Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now