Reverse string 2

[Domino2]

Is there an efficient way to reverse a string containing a name?

Last Name/First Name
Last Name/Middle Name,First Name

so they read

First Name/Last Name
First Name/Middle Name/Last name

Thanks

 

[Remou]

You may wish to look at this FAQ:

http://www.tek-tips.com/faqs.cfm?fid=6468

 

[MajP]

Yes. Design your database correctly so that each part is its own field. Then you can display a name in any possible format. This is non normalized data (non atomic).

lastName
firstName
midName

 

[Domino2]

Thnks Remou, very useful link. Thanks Majp, however if I had individual name parts then it would be a piece of cake, but as I said I have a string to deal with.

 

[SeeThru]

Try this set of three functions. They are a little cruude, but will work fine on most (UK) English names....

They look for a title from a known list at the start, take the last word as a surname and the rest as first name. A single word on it's own is assumed as a first name, but a title and one word is assumed as a lat name.

This is not identical to your setup, but you should be able to adapt them to expect a format of lastname, firstname

Public Function ExtractTitle(StrFullName As Variant) As Variant
Dim TempTitle As String

'First of three functions to extract Title, First Name and Last Name from a
'test field with a name in the format 'Mr John Doe', 'Mr Doe', 'John Doe'
'John, etc. To add to list of known titles, enter it below.

If IsNull(StrFullName) Or StrFullName = "" Then
    ExtractTitle = Null
    Exit Function
End If

If InStr(1, StrFullName, " ") = 0 Then
    TempTitle = "" 'no spaces. Assume first name only
Else
    TempTitle = Left(StrFullName, InStr(1, StrFullName, " ") - 1)
End If

If TempTitle = "Mr" Or TempTitle = "Mrs" Or TempTitle = "Dr" Or TempTitle = "Sir" _
Or TempTitle = "Cptn" Or TempTitle = "Ms" Or TempTitle = "Miss" Or TempTitle = "Prof" Then
    
    ExtractTitle = Left(StrFullName, InStr(1, StrFullName, " ") - 1)
Else
    ExtractTitle = Null
End If
End Function

Public Function ExtractFName(StrFullName As Variant) As Variant

On Error GoTo FName_Err_Handler

Dim intStartPos, intEndPos As Integer
'See previous function. Depends on ExtractTitle.

If IsNull(StrFullName) Or StrFullName = "" Then 'exit if invalid input
    ExtractFName = Null
    Exit Function
End If

If InStr(1, Trim(StrFullName), " ") = 0 Then 'if only one word, accept as fname
    ExtractFName = StrFullName
    Exit Function
End If

'find start
If IsNull(ExtractTitle(StrFullName)) Then
    intStartPos = 1 'if there is no title, start at 1
Else
    intStartPos = InStr(1, StrFullName, " ") + 1 'otherwise, start at first space
    If InStr(intStartPos, StrFullName, " ") = 0 Then 'there is only title and last name
        ExtractFName = Null
        Exit Function
    End If
End If
'find end (last space)
intEndPos = 0
Do While InStr(intEndPos + 1, StrFullName, " ") > 0 'find position of last space
    intEndPos = InStr(intEndPos + 1, StrFullName, " ")
Loop
If Trim(Mid(StrFullName, intStartPos, intEndPos - intStartPos)) = "" Then
    ExtractFName = Null 'null if the result is zero length)
Else
    ExtractFName = Mid(StrFullName, intStartPos, intEndPos - intStartPos)
End If
Exit Function

FName_Err_Handler:
ExtractFName = Null

End Function

Public Function ExtractLName(StrFullName As Variant) As Variant

'finds last section (after a space) of a string.
Dim i, intSpaceCount As Integer, strTemp As String

If IsNull(StrFullName) Or StrFullName = "" Then
    ExtractLName = Null
    Exit Function
End If

If InStr(1, StrFullName, " ") = 0 Then
    ExtractLName = Null 'if there is no spaces assume it is a first name.
Else
    intSpaceCount = 0
    strTemp = StrFullName
    Do While InStr(strTemp, " ") > 0 'count the number of spaces in the string
        intSpaceCount = intSpaceCount + 1
        strTemp = Mid(strTemp, InStr(1, strTemp, " ") + 1, Len(strTemp))
    Loop
    strTemp = StrFullName
    For i = 1 To intSpaceCount 'grab the text after the last space
        strTemp = Mid(strTemp, InStr(1, strTemp, " ") + 1, Len(strTemp))
    Next i
    
    If Trim(strTemp) = "" Then
        ExtractLName = Null
    Else
        ExtractLName = Trim(strTemp) 'return variant
    End If
End If

End Function

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[Domino2]

Many thanks SeeThru, the more the merrier.