Which Means
1) It needs to know what prefixes are Mr. Mrs. Miss Ms., Capt., Col., Rev., (this is the list)

2) It needs to include first and/or middle initials in the firstname field.

3) The company name usually has no spaces in it (thus the underscore) so the brackets need to be deleted (if they exist in the field) and the underscores replaced with spaces (I can do this part I think)

It's quite a bit of logic here.. Not sure how or where to start.

I am outputting it to a text file so that somebody else's program can bring it in.

Open "C:\temp\namelist.txt" For Output As #intnumfile

With Data1.Recordset
Do Until .EOF

I get lost at this point

At the end there are other parts of the file that I have already parsed and added to the line below:

Here is a function that I wrote to handle something like this, except I only needed the Last name, first initial. I tested it with a format that you will need and it does not work, but you can see the logic and modify it. I don't know of an easy way to do this, but this could be a good starting point for you. Hope it helps.

if i = 0 then '' check if it's a prefix
blnFoundPrefix = false
for j = 0 to UBound(arrPrefix)
if arrPrefix(j) = arrNames(i) then
blnFoundPrefix = true
newName(0) = arrNames(i)
exit for
end if
next
if blnFoundPrefix <> true then '' not a prefix
'' find out how many words there were
if UBound(arrNames) = 1 then '' it's alone so
newName(2) = Chr$(34) & arrNames(i) & Chr$(34)
else if UBound(arrNames) = 2 then '' it's a first and last name
newName(1) = Chr$(34) & arrNames(0) & Chr$(34)
newName(2) = Chr$(34) & arrNames(1) & Chr$(34)
end if
end if

Case Else: '' three or more, so check for initials
'' check if there's a prefix
blnFoundPrefix = False
For i = 0 To UBound(arrPrefix)
If arrNames(0) = arrPrefix(i) Then
blnFoundPrefix = True
Exit For
End If
Next

'' basically add everything in the middle to first name
'' when we get to the last word, we have the last name and we are
'' done. Assuming that there is always a last name. if not let me know
numWords = numWords - 1 '' i added 1 earlier
For j = 1 To numWords
If j = numWords Then '' it's the last one, must be last name?
newName(2) = Chr$(34) & arrNames(j) & Chr$(34)
Else
newName(1) = newName(1) & " " & arrNames(j)
End If
Next
newName(1) = newName(1) & Chr$(34) & ","

End Select

'' set up the output string
For i = 0 To UBound(newName)
If newName(i) <> "" Then
strNewName = strNewName & newName(i)
End If
Next
parseName = strNewName

End Function

it does make some assumptions (which i put in comments)
let me know if you need tweaking (which you probably will).

As I look further at your results, I see that the logic is actually pretty simply:

1) Split the name at the spaces
2) Pull off the last item and place into the third field (modifying as necessary if it's a company)
3) Pull off the first item and see if it matches a predefined title.
a) If so, place into the first field and put the rest (joined) into the second field
b) If not, place it with the rest (joined) into the second field.

0

trudymareschAuthor Commented: 2002-05-02

rspahitz
Basically - that is it in a nutshell - I tried splitting at the spaces and kept getting duplication in the parse.