String handling (right to left)

[neileg]

I have a data set with a name field that contains names and initials. These are in the form:
A B Smith
C Jones
David Brown

I want to be able to extract just the last name, ie Smith, Jones, Brown

I don't mind doing this in Access or Excel. It is part of a one off data cleanse.

This is easy if the surname and intiitals were reversed using InStr or Find to identify the first space and then Left. I can't figure out how to do it with the names and intitials the way they are.

Any bright ideas?

 

[Mile-O]

Use these funtions to reverse the string, extract the surname and then reverse the string again.

Function ReverseString(ByVal strDefault As String) As String

    Dim i As Integer, strNewString As String
    
    For i = Len(strDefault) To 1 Step -1
        strNewString = strNewString & Mid(strDefault, i, 1)
    Next i
    
    ReverseString = strNewString

End Function

Function RemoveFirstWord(ByVal strDefault As String) As String

    RemoveFirstWord = left(strDefault, InStr(1, strDefault, " ") - 1)

End Function

Private Sub Command2_Click()
    Text0.SetFocus
    Text0 = ReverseString(Text0.Text)
    Text0 = RemoveFirstWord(Text0.Text)
    Text0 = ReverseString(Text0.Text)
    
End Sub

 

[Tracy]

Use Instr to find the position of the first space:

PositionOfSpace = Instr(1,namefield," ")

Then use Instr again to find the position of the second space, but start searching the field one character after the first space:

PositionOfSpace = Instr(PositionOfSpace+1, namefield," ").

Keep using Instr until 0 is returned, which means there are no more spaces. The last value of PositionOfSpace + 1 will be the start position of the surname.

Then use the Mid function to retrieve the surname, using a start position of PositionOf Space + 1:

Mid(namefield, PositionOfSpace + 1)

 

[neileg]

Looks good.

Trouble is, I now find a big chunk of the records are reversed, i.e. surname, intitial(s)

I'm trying to think of a way of extracting a string of non blank chars with a length of 3 or more.

 

[Mile-O]

The way I gave you should leave you with just the surname.

If you look to extract strings of 3 non-blank characters or more you might end up with incorrect data, especially if some of the entries in your table contain David Brown
as mentioned in your example.

And I just noticed that three lines in my example can become one with this line:

Text0 = ReverseString(RemoveFirstWord(ReverseString(Text0.Text)))

 

[neileg]

Quite right, Mile-O-Phile. Thank you.

I think I'm going to settle of a bit of cleverness as suggested by you, and a bit of ignorance, by passing the really messy stuff back to the originator!

Thanks for the help.

Must be quiet in Purgatory, this afternoon?

 

[Mile-O]

Always is quiet here.

Just a couple of penguins who say they are "just passing through" and myself.