Instring Function Script

[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

[missinglinq]

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

Linq ;0)>

[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

[ADezii]

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

[missinglinq]

Here? Where?

Linq ;0)>

[NeoPa]

Leave him alone Linq!

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

[missinglinq]

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

Linq ;0)>

[NeoPa]

No. I checked ;)

There was nothing else there :)

[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.

[missinglinq]

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

Linq ;0)>

[ADezii]

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

Linq ;0)>

Long, Short, and Intermediate! (LOL).

[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

[NeoPa]

; - )

[Supermansteel]

I am going back to the drawing board on this one. I was just informed that the last name of this is not working correctly. Using this function:
LastName = Right([CN],Len([CN])-InstrRev([CN]," "))
It is Giving me the III or JR or SR instead. So if a name looks as such:
Alfred J Hitchcock Jr
Its giving me the Jr.
There were 5 instances that this happened in out of 200. Is this something I will need to do manually or is there a better way of writing this.

Please help,

Thanks,

Matt

[NeoPa]

You have to consider first what concept you will use to try to determine what should be recognised as the "Last Name". Usually, it's the last word in the field. If this is not right for you then you need to consider and specify, what logic you intend to use.

Turning the logic into code is only straightforward if the logic is known. Only you know your requirements.