CODEWITHSUNDEEP
excelvba
thevancanfan
thevancanfan

Reputation: 29

Split full name into lastname,firstname if lastname has multiple parts (i.e. van, de)

I have a list with multiple names, however, some names have multiple last names: Ex. "Eddie van Halen. I can't get my code to output: "van Halen, Eddie", instead it outputs: "Van, Eddie"

Would there be a way to check if names() has more than 2 parts, and if so to include names(1) & names(2) as last name instead of checking for "van". That is if a last name includes other parts such as "de".

Additionaly, if the full name does not have multiple parts, ex: "volunteer", the code should skip this name.

Here is my current code:

Sub Filter()

Dim r As Range
Dim c As Range
Dim names() As String
Dim lastrow As Long

lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

With ActiveSheet

Set r = .Range("K3:K" & lastrow)

For Each c In r
    names = Split(c.Value, " ")

    If IsEmpty(c.Value) Then Exit Sub

    ElseIf InStr(c.Value, "van") > 0 Then

        c.Value = names(1) & names(2) & ", " & names(0)

    Else
        c.Value = names(1) & ", " & names(0)

    End If
Next c

End With

End Sub

Upvotes: 1

Views: 143

Answers (2)

JohnyL
JohnyL

Reputation: 7142

As an option, you could use regular expression. The following regular expression matches all words which do not begin with van, von, de etc. You can add your words at will. As an edge case, the name itself can start with van or von (for instance, Vonder). In order to handle this case, I have added \b into match of the exclusion, so these prefixes must be stand-alone. The other case, as @ScottCraner noted, is three parts names (like Mary Lou Smith in his example). In this case you can maneuver these cases with Count of matches (x variable). For instance, you can concatenate any parts, if there are three names.

Sub F()

    '// Tools -> References -> Microsoft VBSscript Regular Expressions 5.5
    Dim re As RegExp, mc As MatchCollection, m As Match, s$, x%

    Set re = New RegExp
    re.IgnoreCase = True
    re.Global = True
    re.Pattern = "\b(?!(van|von|de)\b)[a-z]+"
    Set mc = re.Execute("van Halen, Vanzen")
    If mc.Count > 0 Then
        For x = 0 To mc.Count - 1
            MsgBox mc(x)
        Next
    Else
        MsgBox "No matches were found.", vbExclamation
    End If

    '// Output:
    '//   Halen
    '//   Vanzen

End Sub

Upvotes: 0

Orbnauticus
Orbnauticus

Reputation: 21

Split takes a third argument, "Limit", which can be used to stop splitting once the result will have that many entries.

    names = Split(c.Value, " ", 2)

In this case names will always have at most 2 elements.

Upvotes: 2

Related Questions