How Can I parse a Full Name into Last, First, MI when I receive it different ways?

The following function works great, I have used it many times. You can receive the name spelled nine different ways, and this will parse it.

Just copy the whole thing into a new module, replace the bolded area's, open the immediate window and type "Call ParseName"

Hope this helps



Function ParseName()
'This function breaks apart a single name field into seperate first, last & middle initial fields.
'This will work for a name entered in any of the following ways:
' {Smith, John} {Smith, John D} {Smith, John D.}
' {Smith,John} {Smith,John D} {Smith,John D.}
' {Smith John} {Smith John D} {Smith John D.}

'Start by adding 3 fields to the table where your single name field is (firstname, lastname, & MI)

On Error GoTo Parse_Err

Dim db As Database
Dim rs As Recordset
Dim fldName As Field
Dim x As Integer
Dim strLast As String, strFirst As String, strMI As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableNameHere", dbOpenDynaset) 'open appropriate table
Set fldName = rs![NameFieldHere] 'single name field

DoCmd.Hourglass True
Do Until rs.EOF
If IsNull(rs!FirstName) Then
x = InStr(1, fldName, ",")
If x = 0 Then
x = InStr(1, fldName, " ")
strLast = Left(fldName, x - 1)
strFirst = Mid(fldName, x + 1)
If Right(strFirst, 1) = "." Then
strMI = Right(strFirst, 2)
strFirst = Left(strFirst, Len(strFirst) - 3)
Else
If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then
strMI = Right(strFirst, 1)
strFirst = Left(strFirst, Len(strFirst) - 2)
Else
strMI = ""
strFirst = strFirst
End If
End If
Else
If Mid(fldName, x + 1, 1) = Chr(32) Then
strLast = Left(fldName, x - 1)
strFirst = Mid(fldName, x + 2)
If Right(strFirst, 1) = "." Then
strMI = Right(strFirst, 2)
strFirst = Left(strFirst, Len(strFirst) - 3)
Else
If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then
strMI = Right(strFirst, 1)
strFirst = Left(strFirst, Len(strFirst) - 2)
Else
strMI = ""
strFirst = strFirst
End If
End If
Else
strLast = Left(fldName, x - 1)
strFirst = Mid(fldName, x + 1)
If Right(strFirst, 1) = "." Then
strMI = Right(strFirst, 2)
strFirst = Left(strFirst, Len(strFirst) - 3)
Else
If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then
strMI = Right(strFirst, 1)
strFirst = Left(strFirst, Len(strFirst) - 2)
Else
strMI = ""
strFirst = strFirst
End If
End If
End If
End If

With rs
.Edit
!LastName = strLast
!FirstName = strFirst
!MI = strMI
.update
.MoveNext
End With
Else
rs.MoveNext
End If
Loop
DoCmd.Hourglass False

rs.CLOSE
db.CLOSE

Parse_Exit:
Exit Sub

Parse_Err:
DoCmd.Hourglass False
MsgBox Err.Number & ": " & ErrDescription
Resume Parse_Exit
End Function

==========================

Jim Lunde
jimlunde@gmail.com