Separate Names

[mccallen60]

Hello,
I am importing a file of doctor names into a MS Access 2003 table from a
text file. Once I import the names, I want to separate the names into
separate fields. Currently the full name is in one field. For example:

NAME
John Q. Smith, MD
Jane Doe, MD, PhD
Tom L. Smith, Jr., DO
Nancy Jones, MD

I want to separate the names into individual fields. For Example:

FIRST NAME MIDDLE NAME LAST NAME SUFFIX DEGREE
John Q. Smith
MD
Jane Doe
MD, PhD
Tom L. Smith Jr
DO
Nancy Jones
MD

Some of the name have a middle initial, some have a suffix, some have
multiple degrees. The good news is all the names are in first name, middle
name (when available), last name, suffix (when available), degree (some with
multiple degrees) format with a space separating them. There are over 13000
records. Can someone offer a solution without me having to re-type these?
Thanks,
Kevin

 

[Dorian]

If you can GUARANTEE there is a period/space after the middle initial and
GUARANTEE there is a comma/space after the surname and GUARANTEE there is a
period/comma/space after the suffix, it's a lot easier. You should also come
up with a list of all used suffixes and degrees.
You need to parse the information based on the separators (space, period,
comma) and the list of possible suffixes and degrees.
This requires a knowledge of VBA. Unless someone happens to have the code
sitting around and can post it or a link to it, you will have to brew your
own. It's not that difficult for a VB programmer.

For instance to get the first name
p = instr(Fullname,' ')
Firstname = left$(Fullname,p-1)
this scans for the first space then takes all prior characters for the first
name.

To get the last name, you could scan for the comma and then take all the
prior characters until you come to a space.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Douglas J. Steele]

Even with those guarantees, you'll still run into problems. How do you
decide that Mary Lou Retton's first name is Mary Lou and that Ludwig von
Beethoven's is only Ludwig? How do you handle one name names like Cher and
Madonna?

This is an extremely difficult task.

 

[kc-mass]

This will only give results as good as the consistency in your data but
worth a try.

Sub Disaggregate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strNameHold As String
Dim strFname As String
Dim strMidName As String
Dim strLName As String
Dim strSuffix As String
Dim strDegrees As String
Dim intStringFound As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("tblNamePlus")
rs.MoveFirst
strNameHold = rs!NameEtAl
Do While Len(strNameHold) > 0 And Not rs.EOF
strNameHold = rs!NameEtAl
Debug.Print strNameHold
intStringFound = InStr(strNameHold, " ")
strFname = Left(strNameHold, intStringFound - 1)
strNameHold = Mid(strNameHold, intStringFound + 1)
If InStr(strNameHold, ".") = 2 Then
strMidName = Left(strNameHold, 2)
strNameHold = Mid(strNameHold, 4)
Else
strMidName = ""
strNameHold = Mid(strNameHold, 1)
End If

intStringFound = InStr(strNameHold, ",")
strLName = Left(strNameHold, intStringFound - 1)
strNameHold = Mid(strNameHold, intStringFound + 1)
intStringFound = InStr(strNameHold, ".")
If intStringFound <> 0 Then
strSuffix = Mid(strNameHold, 2, intStringFound - 1)
strNameHold = Mid(strNameHold, Len(strSuffix) + 4)
Else
strSuffix = ""
strNameHold = Mid(strNameHold, 2)
End If
strDegrees = strNameHold
strNameHold = Mid(strNameHold, Len(strNameHold))
rs.Edit
rs!fname = strFname
rs!MidName = strMidName
rs!LName = strLName
rs!Suffix = strSuffix
rs!Degree = strDegrees
rs.Update
rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing
End Sub


Yeilds data like

tblNamePlus NameEtAl Fname MidName LName Suffix Degree
John Q. Smith, MD John Q. Smith
MD
Jane Doe, MD, PhD Jane
Doe
MD, PhD
Tom L. Smith, Jr., DO Tom L. Smith Jr. DO
Nancy Jones, MD Nancy
Jones
MD


Regards

Kevin