Solved Pulling Multiple Last Names From String (1 Viewer)

[pooldead]

I am trying to extract last names from list of full names. I've got the standard down to pull a common first-last name (John Smith). I am running into a problem with names that have multiple last names (John Smith Anderson). Here is the code I have that works for the common name:

trimStr = Trim(Right(str, InStr(str, " ")))

Is there a relatively straight-forward way to handle names like this? I imagine I would have to use an If-Then to identify whether the full name has a single or multiple last names, but I'm not sure how to manipulate the multiple last name scenario.

 

[Ranman256]

there is only 1 last name. What do you want to do with the middle name?
is there a field for middle?

 

[Isaac]

trimStr = Right(str, Len(str) - InStr(1, str, " "))

 

[pooldead]

@Ranman256 - There isn't, but the data set I'm pulling from doesn't maintain middle names. In my example, you would consider "John" to be the first name and "Smith Anderson" to be the last name.

@pisorsisaac@gmail.co - Thanks! I figured that Len() would be a part of it somewhere, but I wasn't sure where to fit it in.

 

[Isaac]

@pisorsisaac@gmail.co - Thanks! I figured that Len() would be a part of it somewhere, but I wasn't sure where to fit it in.

Absolutely happy to help.

 

[plog]

Is there a relatively straight-forward way to handle names like this?

No. Not only can a computer not do this, unless a human is familiar with the person, a human cannot do this:

Anna Nicole Smith
John Von Neuman
BIlly Ray Cyrus
Oscar De La Hoya

My advice is to handle them case by case. Have the computer do the names where only 2 are present, then have a query to diisplay those which have more than 3 names so that a human can decide how they should be broken up.