Splitting Text

[psrussell]

I need to split some existing text data into seperate fields within a table. For example, the existing field may contain various combinations of a persons name i.e. "Joe Andrew Bloggs", "Joe Bloggs" or "Joe A Bloggs". The new table will contain three fields, FirstName, MiddleName, and LastName.

Now can I update the FirstName field with "Joe", then update the MiddleName field with either "Andrew" or "A" and finally the LastName field with "Bloggs"?

I've had a read about Lefts, Mids, and Rights but they dont appear to achieve what I'm looking for . Can anyone guide me in the right direction?

Many thanks

 

[24sharon]

Try it

FullName = "abc def gh"
x = Strings.Split(FullName, Chr$(32))
For i = 0 To UBound(x)
MsgBox x(i)
Next

 

[ChrisO]

I don’t know what version you are using but if you don’t have the Split function you can give this a shot.

Sub TestIt()
    Dim lngPos1     As Long
    Dim lngPos2     As Long
    Dim FirstName   As String
    Dim MiddleName  As String
    Dim LastName    As String
    Dim strFullName As String
    
    strFullName = Trim$("   Joe    A      Bloggs   ")
    
    lngPos1 = InStr(1, strFullName, " ")
    
    If lngPos1 <> 0 Then
        FirstName = Left$(strFullName, lngPos1 - 1)
        lngPos2 = Len(strFullName)
        
        While Mid$(strFullName, lngPos2, 1) <> " "
            lngPos2 = lngPos2 - 1
        Wend
        
        MiddleName = Trim$(Mid$(strFullName, lngPos1 + 1, lngPos2 - lngPos1 - 1))
        LastName = Trim$(Mid$(strFullName, lngPos2 + 1))
    End If

    MsgBox "-" & FirstName & "-" & MiddleName & "-" & LastName & "-"

End Sub

Hope that helps.

Regards,
Chris.

 

[psrussell]

Splitting Text - Many thanks

Thanks to all that replied to my cry for help. Your information will be very useful and gratefully received.