Help separating names 1

[bill420]

I have a name field that can contain more than one name, the names are always separated by “and” (i.e. Thomas S. Smith and Tracey L. Smith). I need to split these names out to separate names on certain forms.
I started with this code:

If InStr(ucName, " and ") Then
ucBuy1 = Left(ucName, InStr(ucName, " and ") - 1)
ucBuy2 = Right(ucName, InStr(ucName, " and ") - 1)

This works some of the time, but there is inconsistency in the results. Sometimes the first few letters are cut from the second name, other times the word “and” is included in the second name.
Is there an easy way to solve this problem?
Thanks in advance.
Bill

 

[MajP]

Public Function name1(theString As String) As String
Dim myArray() As String
myArray = split(theString, "and")
name1 = Trim(myArray(0))
End Function

Public Function name2(theString As String) As String
Dim myArray() As String
myArray = split(theString, "and")
name2 = Trim(myArray(1))
End Function

 

[PHV]

ucBuy1 = Left(ucName, InStr(ucName, " and ") - 1)
ucBuy2 = Mid(ucName, InStr(ucName, " and ") + 5)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bill420]

Thanks for the fast replies.
PHV, your solution worked perfect
Thanks so much
Have a star.
Bill