I need a function to pick out different parts of names. I don't have a lot of time here at work. If anyone has time, I REALLY would love your help.

This is the way names can come when I read in the data:

Jones, Jeffrey S
Means Marilyn
Lawson Jay D
Lopez, Manuel B.
Lowe Jr Sam D
Smith III Trevor L

The function I have now is far from perfect. I REALLY could use your help. I was thinking of measuing the length of the variable and by what length it was, I would use a different thing. Well, this is what I have so far:


VB Code:
  1. Function LastName(ByVal WholeName As String) As String 

  2.         Dim arr() As String 

  3.         Dim LName, index As String 

  4.  
  5.         WholeName = Replace(WholeName, ",", " ") 

  6.         arr = Split(WholeName, " ") 

  7.  
  8.         index = WholeName.IndexOf(" ") 

  9.         If index = -1 Then 

  10.             LName = "" 

  11.         Else 

  12.             If arr(1) = "III" Or arr(1) = "JR" Or arr(1) = "SR" Or arr(1) = "II" Then 

  13.                 Try 

  14.                     LName = arr(0) + " " + arr(1) 

  15.                 Catch ex As Exception 

  16.                     LName = "" 

  17.                 End Try 

  18.             Else 

  19.                 Try 

  20.                     LName = arr(0) 

  21.                 Catch ex As Exception 

  22.                     LName = "" 

  23.                 End Try 

  24.             End If 

  25.         End If 

  26.         Return Trim(LName) 

  27.  
  28.     End Function 

  29.  
  30.     Function FirstName(ByVal WholeName As String) As String 

  31.         Dim arr() As String 

  32.         Dim FName As String 

  33.         Dim index As Integer 

  34.  
  35.         WholeName = Replace(WholeName, ",", " ") 

  36.         arr = Split(WholeName, " ") 

  37.  
  38.         index = WholeName.IndexOf(" ") 

  39.         If index = -1 Then 

  40.             FName = "" 

  41.         Else 

  42.             If arr(1) = "III" Or arr(1) = "JR" Or arr(1) = "SR" Or arr(1) = "II" Then 

  43.                 Try 

  44.                     FName = arr(2) 

  45.                 Catch ex As Exception 

  46.                     FName = "" 

  47.                 End Try 

  48.             Else 

  49.                 Try 

  50.                     FName = arr(1) 

  51.                 Catch ex As Exception 

  52.                     FName = "" 

  53.                 End Try 

  54.             End If 

  55.         End If 

  56.         Return Trim(FName) 

  57.  
  58.     End Function 

  59.  
  60.     Function MiddleName(ByVal WholeName As String) As String 

  61.  
  62.         Dim arr() As String 

  63.         Dim MName, index As String 

  64.  
  65.         WholeName = Replace(WholeName, ",", " ") 

  66.         arr = Split(WholeName, " ") 

  67.  
  68.         index = WholeName.IndexOf(" ") 

  69.         If index = -1 Then 

  70.             MName = "" 

  71.         Else 

  72.             If arr(1) = "III" Or arr(1) = "JR" Or arr(1) = "SR" Or arr(1) = "II" Then 

  73.                 Try 

  74.                     MName = " " + arr(3) 

  75.                 Catch ex As Exception 

  76.                     MName = "" 

  77.                 End Try 

  78.             Else 

  79.                 Try 

  80.                     MName = " " + arr(2) 

  81.                 Catch ex As Exception 

  82.                     MName = "" 

  83.                 End Try 

  84.             End If 

  85.         End If 

  86.         Return MName 

  87.  
  88.     End Function

PLEASE HELP! Thanks!

Brenda