Extract surname from text string if exists

[rgrimes]

I currently have a module that allows me to take the employee name field and break into individual fields (e.g. Last name, First name, Middle name); however, I also have instances where employees have surnames or military ranks (e.g. Jr, II, CDR, ENS, etc...) included in the employee name field. The code that I have used works great for extracting the First, Last, and Middle names, but I haven't been able to get anything to work to break out the surname or rank (if exists). I have searched the internet and have found many examples that do what I already have, but I can't seem to find the extra piece that I need. Since I don't work with code often, I would greatly appreciate any help.

My current code I'm using is:

 Function SplitNames(strName As String, strPart As String) As String
 Dim arrSplit() As String
 arrSplit = Split(strName, ",", -1)
 Select Case UCase(strPart)
    Case "L"
      SplitNames = Nz(Trim(arrSplit(0)), "")
   Case "F"
      SplitNames = Nz(Split(Trim(arrSplit(1)))(0), "")
   Case "M"
      SplitNames = vbNullString
      If InStr(Trim(arrSplit(1)), " ") Then SplitNames = Split(Trim(arrSplit(1)))(1)
    Case Else
        If Trim(strName & "") = "" Then
             SplitNames = vbNullString
   Exit Function
End If
       
End Select
 End Function

 

[RuralGuy]

You may want to show what sort of strings are used to invoke this function. A few examples might not hurt either.