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!!

 

[kjv1611]

Are there ever middle names involved, or Suffixes such as Jr or Sr? If not, then I think you can do this easier...

You'll need to first see how many space characters are in the cell. Then if there is only one space, use the Find String function to find the precise location of the one space, and use the Right() Function to get all text to the right of that location. If there are 2 spaces, then obviously, you'll start from the 2nd space.

Make sense? If you need more help with the specifics, I'll try to post back later, but I'm sure someone will already have helped by then. It won't be as hard as you might think. The hard part will just be determining all the variables.

--

"If to err is human, then I must be some kind of human!" -Me

 

[xlhelp]

=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SkipVought]

xlhelp, I think that the OP wanted

[tt]Givenname MY SUR NAME[/tt]

the

[tt]CAP PART[/tt]

of the string to be sonsidered surname.


Skip,

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

 

[xlhelp]

Excusez-moi! Reading full text is has never been my forté.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SkipVought]

I was waiting for you to hit this one. My inclination would be to code a VBA function.

Skip,

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

 

[SkipVought]

like this...

Function SUR_NAME(str As String, Optional Last As Boolean = True)
    Dim a, i As Integer
    a = Split(str, " ")
    For i = 0 To UBound(a)
        If Last Then
            If a(i) = UCase(a(i)) Then
                SUR_NAME = SUR_NAME & a(i) & " "
            End If
        Else
            If a(i) = Application.Proper(a(i)) Then
                SUR_NAME = SUR_NAME & a(i) & " "
            End If
        End If
    Next
    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!}

 

[dhookom]

Another function that should work:

Public Function GetSurName(strFullName As String) As String
    Dim intStartSurName As Integer
    Dim intI As Integer
    Dim str2Chars As String
    Dim str2CharsUpper As String
    For intI = 1 To Len(strFullName) - 1
        [green]'grab 2 characters[/green]
        str2Chars = Mid(strFullName, intI, 2)
        str2CharsUpper = UCase(str2Chars)
        [green]'ignore spaces and compare binary values[/green]
        If InStr(str2Chars, " ") = 0 And _
                StrComp(str2Chars, str2CharsUpper, _
                    vbBinaryCompare) = 0 Then
            [green]'if the same, exit the loop[/green]
            Exit For
        End If
    Next
    GetSurName = Mid(strFullName, intI)
End Function

Duane
Hook'D on Access
MS Access MVP

 

[HarleyQuinn]

Are all the surnames in capital letters?

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

http://lessthandot.com

 

[SkipVought]

Are all the surnames in capital letters?

That's what it appears...

To allow for instances where the surname may have more than 1 than one name eg. Givenname MY SUR NAME ...

Skip,

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

 

[xlhelp]

Will this work?
=SUBSTITUTE(A2,LEFT(A2,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),"")

I might look above replies later.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SkipVought]

xl,

Just returns the LAST word.

Skip,

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

 

[HarleyQuinn]

If that's the case, we could do something like this:

Function GetSurname(fullname As String) As String
Dim re As Object
Dim m As Variant

Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .Pattern = "[A-Z]+?(\s|$){1,}"
    For Each m In .Execute(fullname)
        GetSurname = GetSurname & m
    Next m
End With

Set re = Nothing

End Function

Hope this helps

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

http://lessthandot.com

 

[webrabbit]

You should be aware that sume surnames start with a lower-case letter, such as terHorst, and that there are very many two word surmames. I don't think you can ever be 100% on this.

 

[HarleyQuinn]

That was the point of my first post, I was trying to see if the surnames the OP was using are all in capitals.

Regards

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

http://lessthandot.com

 

[Lilliabeth]

FIND the first instance of double capitals in the string, input this as the start point for a MID

I think he feels his list always has the surname in caps.

--Lilliabeth

 

[xlhelp]

xl, Just returns the LAST word

I beieve this will give any space you want to find. Assuming two first names (1st and middle) this will give you whatever remains. You can replace the red number 2 to find which ever numbered space you wish to find.

=RIGHT(A2,LEN(A2)-SEARCH(CHAR(127),SUBSTITUTE(A2," ",CHAR(127),[red]2[/red])))

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[TonyJollans]

I'm out of practice with Excel, but this finds the position of the last lower-case character and takes everything after it, which, I think, is equivalent to what was asked for - there's probably an easier way, of course:

Array enter (with Ctrl+Shift+Enter):
[blue][tt]=TRIM(RIGHT($A1,LEN($A1)-MAX(IF(CODE((MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)))>= CODE("a"),ROW(INDIRECT("1:"&LEN($A1))),0))))[/tt][/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[Adam72]

Hi All

Thanks for your replies. To answer the question:
The extraction process converts the surname field into capitals before the concatenation, so yes the surname is always in capitals even when it should be in lower eg van der Veldt or de Bertoli. The givennames are always in 'Propercase' eg Adam.

Whilst these surnames are in the minority would rather write a complete solution rather than require user interference ummmm I mean intervention.

Will work through the solutions proposed today and let you all know which works best for me. Really appreciate your help.

 

[Adam72]

Hi All (again)

My thanks goes to you all, it has been great to get so many responses in a short amount of time. But in the end I had to go with Skip's solution as it was the more robust when placed against my testing names (see attached).

Skip, any chance you could give me a quick run down of each line? I am new to programming and would like to better understand what each line is doing.

Thanks everyone


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