Code to extract surname 1

[Adam72]

I have an excel file which contains the full name of clients which has already been concatenated, and need to extract the surname back out of the string to be able to sort alphabetically on surname. The names are in the following format:

Givenname Givenname SURNAME
Givenname SURNAME
etc

To allow for instances where the surname may have more than 1 than one name eg. Givenname MY SUR NAME I need to develop a code to FIND the first instance of double capitals in the string, input this as the start point for a MID command to extract the surname and then paste it into another cell on the same line, then move onto the next row.

Appreciate any help!!

 

[Adam72]

hmmm upload of file not working... sorry





Adam72 is Australian... does that explain it??
Every day you wake up is a good day... just remember the alternative!!

 

[SkipVought]

'[b]paste this function into a MODULE and it can be used like any other spreadsheet function.
'reference the cell containing the name string, and the LAST NAME (in caps) will be returned.
'use FALSE as the second OPTIONAL argument to return the Given Name (In Proper)[/b]
Function SUR_NAME(str As String, Optional Last As Boolean = True)
    Dim a, i As Integer
'[b]the name string is split on SPACES into the a variable as an array[/b]
    a = Split(str, " ")
'[b]process the array for each or its elements[/b]
    For i = 0 To UBound(a)
'[b]if the optional argument Last, is TRUE[/b]
        If Last Then
'[b]if the element EQUALS UPPERCASE, keep it with a SPACE[/b]
            If a(i) = UCase(a(i)) Then
                SUR_NAME = SUR_NAME & a(i) & " "
            End If
'[b]if the optional argument Last, is FALSE[/b]
        Else
 '[b]if the element EQUALS Proper, keep it with a SPACE[/b]
           If a(i) = Application.Proper(a(i)) Then
                SUR_NAME = SUR_NAME & a(i) & " "
            End If
        End If
    Next
'[b]if the LENGTH of SUR_NAME > 1 then chop the last character (SPACE)[/b]
    If Len(SUR_NAME) > 1 Then SUR_NAME = Left(SUR_NAME, Len(SUR_NAME) - 1)
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Adam72]

Okay results were as below.

Name Skip Duane Andy
Test ONE ONE ONE ONE
Test Tester TWO TWO TWO TWO TWO TWO TWO TWO
Test D'THREE D'THREE D'THREE THREE
Test Tester FOUR-FOUR FOUR-FOUR FOUR-FOUR FOUR
Test FIVE FIVE-FIVE FIVE FIVE-FIVE FIVE FIVE-FIVE FIVE FIVE
Test-Tester SIX SIX -Tester SIX SIX
T'est SEVEN SEVEN T'est SEVEN SEVEN

Adam72 is Australian... does that explain it??
Every day you wake up is a good day... just remember the alternative!!

 

[HarleyQuinn]

Hmm, yes, I'd forgotten to allow for other characters in surnames

For completeness, a slightly more useful variation of mine could be produced by changing the pattern to:

.Pattern = "[A-Z]+?([^a-z]|$){1,}"

Regards

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[strongm]

If I were going with the regular expression solution, I'd be tempted to modify ity a bit:

[blue]Public Function GetSurname(fullname As String) As String
Dim m As Variant

With CreateObject("VBScript.RegExp")
    .Pattern = " ([A-Z '-]{2,})"
    For Each m In .Execute(fullname)
        GetSurname = m.submatches.Item(0)
    Next m
End With

End Function[/blue]

 

[HarleyQuinn]

Nice Mr Strong.

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[kwbMitel]

I recently had to do this exact exercise but I did not have the benefit of the last name being all caps.

The names were separated by spaces however as in this case.

I used a formula to locate the last space in the "Combined" name based on the difference in length of the name with and without spaces.

This formula returns all characters after the last space in the Text.

RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))


*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[SkipVought]

The general problem with this kind of exersize is that you often have surnames that are more than one word in length. You may also have Jr/Sr/III, Esq etc following the surname. You may have middle intitials. You may have inconsistent formats. What else am I missing???

So there's an effort needed to categorize the data in order to apply the correct parsing. This part can be quite messy, and VERY labor intensive.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}