parse out name query

[joe789]

Hi Folks,

I have a name that is formatted in the following manner:

LN; FN MI

Example:

1. Smith; John Q (person has middle initial populated)
2. Smith; Jane (person does not have middle initial populated)
3. Jack III, Citizen (person has suffix after name with no middle initial populated)
4. John III, Citizen M (person has suffix after name with middle initial populated)
5. Johnson; Jane M. (person has middle initial populated but a period after the initial)

I am attempting to parse out the names using Instr, Mid, Right, Left, etc and need a little help please.

In trying to arrive at last name, I am using
Left([Name],InStr(1,[Name],";")-1)
which works nicely for all last names in all the situations that I can find.

In trying to arrive at the first name, I am using
Mid([Name],InStr(1,[Name]," ")+1)

which extracts the first name but includes the middle initial after the first name as well. I know that for the Mid statement I can instruct the command to stop grabbing characters but when I try to do so, it just doesn't work out. Because there is a space between first name and middle initial (when middle initial is populated), I can tell the Mid statement to stop grabbing characters after it hits the second space (since the first space is between the last name and first name and therefore must be ignored) ... but when I try to write that up, it doesn't return anything:

Mid([Name],InStr(1,[Name]," ")+1,InStr(InStr([Name]," ")," "))


Then there is the middle initial to contend with, which at this point if I cannot extract that part easily that is OK, but it would be nice.

Any help would be greatly appreciated.

Thank you,

Joe

 

[jdraw]

Here is a routine that I use to parse names.
I have adjusted it to work with the semicolon ";"

It can return First, Last or Middle.
I'm including a test proc as well.

TestRoutine

Sub TestNameOct19()
Dim sname(4) As String
sname(0) = "del la Hoya;Oscar"
sname(1) = "O'Brien;Megan M"
sname(2) = "Smith; John Q"
sname(3) = "Payne;Ima"
sname(4) = "Del Carrico;Efriam O."
Dim i As Integer

For i = 0 To 4
Debug.Print ParseName(sname(i), "L") & " " & ParseName(sname(i), "F") & " " & ParseName(sname(i), "M")
Next
End Sub

ParseName Function

'---------------------------------------------------------------------------------------
' Procedure : ParseName
' Author : Jack
' Created : 2/23/2010
' Purpose : To parse a field containing the person's full name and to return
' the first name, or the initial if it exists, or last name depending on the
' value of strWhich.
'
' NOTE: The format of the fullname field is
' Lastname; Firstname Initial(may not be present)
' eg a)De Jesus; Charlene K.
' b)O'Sullivan; Margaret
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: strname == the person's fullname
' strWhich = F First Name
' = M Middle Initial
' = L Last Name
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function ParseName(strName As String, strWhich As String) As String


Dim strUtil As String
Dim strLastname As String
Dim strFirstname As String
Dim strMiddle As String
On Error GoTo ParseName_Error

strUtil = Trim(strName)
strLastname = Left(strUtil, InStr(1, strUtil, ";") - 1)
strMiddle = Mid(strUtil, InStrRev(strUtil, " ") + 1)
If Len(strMiddle) <> 1 Then
strMiddle = vbNullString
Else
ParseName = strMiddle
strUtil = Mid(strUtil, 1, Len(strUtil) - 2)
End If
strFirstname = LTrim(Mid(strUtil, InStr(1, strUtil, ";") + 1))

Select Case strWhich
Case "F"
ParseName = strFirstname
Case "L"
ParseName = strLastname
Case "M"
ParseName = strMiddle
Case Else
ParseName = vbNullString
End Select


On Error GoTo 0
Exit Function

ParseName_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ParseName of Module Module4"

End Function

I hope it's useful to you.