Instring Function Script

[NeoPa]

; - )

[ADezii]

Here is a little, General Template, (the one that NeoPa and Linq missed), which will parse a Full Name consisting of a First, Last, and Optional Middle Name/MI, into its constituent parts. It ignores Names with double spaces, Prefixes and Suffixes, Titles, etc.

Public Function fParseName(strName As String)
Dim varNameParts As Variant
Dim intCounter  As Integer
Dim strFirstName As String
Dim strLastName As String
Dim strMI As String

varNameParts = Split(strName)

Select Case UBound(varNameParts)
  Case 1    'First and Last Name
    strFirstName = varNameParts(0)
      Debug.Print strFirstName
    strLastName = varNameParts(1)
      Debug.Print strLastName
  Case 2    'First, MI, and Last Name
    strFirstName = varNameParts(0)
      Debug.Print strFirstName
    strMI = varNameParts(1)
      Debug.Print strMI
    strLastName = varNameParts(2)
      Debug.Print strLastName
 Case Else  'anything goes Dr. Tom Clancy III
   Debug.Print "Not a standard naming convention"
End Select
End Function

? fParseName("Alfred E. Neumann")
Alfred
E.
Neumann

? fParseName("Alfred Eugene Neumann")
Alfred
Eugene
Neumann

? fParseName("Dr. Thomas Cook III")
Not a standard naming convention

[ADezii]

I understand that too much smoke eating can negatively affect long term memory!

Linq ;0)>

Long, Short, and Intermediate! (LOL).

[missinglinq]

I understand that too much smoke eating can negatively affect long term memory!

Linq ;0)>

[ADezii]

==> Linq & NeoPa, you gentlemen do not have the necessary clearance to view it. Actually, I forgot to Post it and it's at work, but as soon as I get back to work, I'll 'try' again to remember to Post the code.

[NeoPa]

No. I checked ;)

There was nothing else there :)

[missinglinq]

I thought maybe it was some of that "stealth" code that can't be seen!

Linq ;0)>

[NeoPa]

Leave him alone Linq!

He's having a little nap and will be back with some code shortly :D

[missinglinq]

Here? Where?

Linq ;0)>

[ADezii]

Here is also a little Routine that I use to extract the First, Last, and possibly Middle Initials from a Name.

[Supermansteel]

LastName = Right([CN],Len([CN])-InstrRev([CN]," "))

Linq ;0)>

Wow that was alot easier than I anticipated and worked perfectly..


Thank you so much.

Matt

[missinglinq]

LastName = Right([CN],Len([CN])-InstrRev([CN]," "))

Linq ;0)>

[Supermansteel]

I am trying to write this instring script on a text field on Customer Name and having difficulties with the last name:
list of customer names:
Bob A George
John Thompson
Paul B Michael

I know the instr function is having difficulties because some of the names have middle letters. For the ones that have a Middle letter it is giving me the results with a space before it _George and the ones that don't have a middle letter it is cutting off the first couple letters. or giving me the last name with the middle letter. I have been trying it all different kind of ways and haven't figure it out
I have tried formulas such as:
Right([CN],InStr([CN]," ")-1)

I have also tried a mid function and haven't been successful. I almost think I need to do an if statement and do it one way if there is a letter and another if it is false, but haven't figured out to write it. Any help on this would be greatly appreciated.

The Left([CN],Instr([CN]," ")-1) works fine for the first name.

Thanks,

Matt