Another Text Manipulation Question...

[DanG]

I know the basics, but can't seem to put them together.
I have several names that come to me...
Fullname:
Schumacher Jr Robert K
Lakewold Jr. Roy

FirstName:
Jr Robert K
Jr. Roy

LastName:
Schumacher
Lakewold

I just want to get the FIRST NAME.
What I've been able to get:
Robert K (wanted to get Robert)
R (wanted to get Roy)

My lame code:

If FName Like "Jr*" Then
FNameFix = Mid(FName, InStr(FName, " ") + 1)
FNameFix = Left(Trim(FNameFix), InStr(Trim(FNameFix), " ") + 1)
Else
FNameFix = FName
End If

I would love any pointers in the right direction
Thank you

 

[Bat17]

This code assumes that the First Name will always be longer that the spare stuff! Could be a problem with
Irvine Revd. Jo
But if you know of special cases like that you can always add more replace lines to take them out first
strNameIn = Replace(strNameIn, " Revd ", " ")

Function GetFirstName(strNameIn As String) As String
Dim aNameIn() As String
Dim strFirstName As String
Dim j As Integer
strNameIn = Replace(strNameIn, ".", "  ")
aNameIn = Split(strNameIn, " ")

strFirstName = aNameIn(1)
For j = 2 To UBound(aNameIn)
    If Len(aNameIn(j)) > Len(strFirstName) Then
        strFirstName = aNameIn(j)
    End If
Next j
'strLastName = aNameIn(0)
GetFirstName = strFirstName
End Function

 

[workmad3]

You need to be able to define exactly how the firstname is going to be obtained, then make sure the code follows this. Computers, unfortunately, can't yet look at a string and say 'this is the firstname and this is the lastname' without being told exactly how to get the firstname, and exactly how to get the lastname.

The lastname is fairly easy, and it looks like you hove it covered in you code. Firstname, from the way the names look to be structured, look like a very big problem.

What might be best would be to split the lastname off, then just using the rest of the string, split it into words, remove any non aplha characters(so .,! etc) and then test the length. From what We've seen above, the firstname is usually going to be the longest of the strings obtained, so you find the longest string and return that.

It does however have flaws(such as if middle names are included as more than just initials) but If the user is told that the name has to be entered in the way you have coded, then it is not your fault if they do it wrong

 

[DanG]

Thanks guys!