Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0314· OpenAIAi2 WildChat-4.8M (AI2)

I have a list of persons’ names in different formats. Each of them is in one Excel cell…

  1. User

    I have a list of persons’ names in different formats. Each of them is in one Excel cell (i.e., each of them is in 1 string). Examples are below: Olive Johnson William F. Hilyard Stanley and Grace Hajicek Phillip Felix Buteau Hazel M. and Mary Louise Van Riper Ditto, Joyce Glenna Roberts Mrs. Lester Norseth Mrs. Mae E. Mason Jennie B. and Charles F. Mills Fred A. and Phyllis M. and Fred G. Bollman A.M. Johnson G.Albert Johnson (Rev.) Olof H. Sylvan C. A. Greek (Cook) Unknown Challstrom Unknown Unknown Knud N Strarup and Alfred Sorenson Frank J. W. Johnson Darlene Rydell and sons Lance and Brandt Rydell Iola Delores Schneider Sturm Unknwon Schmidt, Unknown Goodenow Fred Brown, Barbara Brown J. Howard Abner, Inez Abner Mike Lewandowski, Kim Lewandowski (Nesler) Ed Cockrell, Leatha Cockrell, Beuford Cockrell Noisworthy, Gary D. Sherre Roy E. Terry, Frederick Terry J. D. Terry, F. Mildred Rev. George Alfred Shadwick, Rachel Pauline Shadwick John Glassnap, Stella Glassnap, Donald Bollinger, Raymond Bollinger Roy W. Friedrich Jr., Judith Friedrich Mr. Albert DeLay, Mrs. DeLay Lillian F. Parsons and Gwendolyn M. Parsons Garen K. Gdanian & Isabelle Gdanian Sarkis Sedefian & Marie E. Kizirian Timothy W. LaMountain & Helen J. LaMountain Dennis A. Rapp & Mary Grates Stoll Rapp Charles P. Green, Jr., M. Reges & R. Reges Green, Their Daughter George R. Brant, Jr. A. Brant, Sr. Roger and Kendra Pelham Karl A. Paulsen & Bertha J. Dittmann Paulsen Leo E. Godin III Alfred Godin Second Joseph J. Olander & Beverly A. Olander, His Wife Susan M. Thirolle & Jean-Claude Thirolle, her husband Jessica B. Arnold and Jake Fred Arnold, her husband

    I need a VBA macro to split them into first_name, last_name, middle_name, title (Mr., Ms., Sr., Rev. and others). Imagine that all names to be processed are in column A, and your output should go to columns B, C, etc. Account for all the scenarios provided in examples above (including absence of some parts in a full name or the fact that they may go in different orders). It is also possible that one cell contain multiple person (for instance, “Stanley and Grace Hajicek”) - in such case put second person's data in the next columns to the first one's

  2. Assistant

    Here's a VBA macro that should suit your needs:

    Sub SplitNames()
        Dim rng As Range
        Dim cell As Range
        Dim NameArray() As String
        Dim i As Integer
        Dim OutputCol As Integer
    
        Set rng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        
        For Each cell In rng
            If cell.Value <> "" Then
                NameArray = SplitName(cell.Value)
                OutputCol = 2
                
                For i = LBound(NameArray) To UBound(NameArray) Step 4
                    cell.Offset(0, OutputCol).Value = NameArray(i)
                    cell.Offset(0, OutputCol + 1).Value = NameArray(i + 1)
                    cell.Offset(0, OutputCol + 2).Value = NameArray(i + 2)
                    cell.Offset(0, OutputCol + 3).Value = NameArray(i + 3)
                    
                    OutputCol = OutputCol + 4
                Next i
            End If
        Next cell
        
    End Sub
    
    Function SplitName(FullName As String) As Variant
        Const TitlePattern As String = "((Mr)|(Mrs)|(Ms)|(Rev)|(Dr)|(Sr)|(Jr))\."
        Const AndPattern As String = "(\s*&\s*|,?\s+and\s+|,?\s+&\s*|,\s+|;\s+)"
    
        Dim Names() As String
        Dim Titles() As String
        Dim i As Integer
        Dim OutputArray() As String
        Dim n As Integer
    
        Names = Split(Replace(FullName, "-", " "), " ")
        n = -1
        
        For i = LBound(Names) To UBound(Names)
            If Names(i) <> "" And Not IsNumeric(Names(i)) Then
                If Names(i) Like TitlePattern Then
                    ReDim Preserve OutputArray(n + 1)
                    OutputArray(n) = Names(i)
                Else
                    If RegexMatch(Names(i), AndPattern) Then
                        n = n + 1
                    Else
                        ReDim Preserve OutputArray(n + 4)
                        
                        If OutputArray(n + 1) = "" Then
                            OutputArray(n + 1) = Names(i)
                        ElseIf OutputArray(n + 2) = "" Then
                            OutputArray(n + 2) = Names(i)
                        Else
                            OutputArray(n + 3) = Names(i)
                        End If
                    End If
                End If
            End If
        Next i
    
        SplitName = OutputArray
    End Function
    
    Function RegexMatch(ByVal StringToSearch As String, ByVal Pattern As String) As Boolean
        Dim Regex As Object
        Set Regex = CreateObject("VBScript.RegExp")
        With Regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = True
            .Pattern = Pattern
        End With
    
        RegexMatch = Regex.Test(StringToSearch)
    End Function
    

    This code defines a macro SplitNames that will process the names in column A and output the split names in columns B, C, D, etc. It uses two helper functions, SplitName and RegexMatch, to split the full name into its component parts and to test strings against regular expression patterns.

    Please note that this code may not cover all possible name variations and corner cases, but it should at least provide a good starting point for further customization if needed.

  3. User

    write VBA code that would transform this data: name Olive Johnson William F. Hilyard Stanley and Grace Hajicek Phillip Felix Buteau Hazel M. and Mary Louise Van Riper Ditto, Joyce Glenna Roberts Mrs. Lester Norseth Mrs. Mae E. Mason Jennie B. and Charles F. Mills Fred A. and Phyllis M. and Fred G. Bollman A.M. Johnson G.Albert Johnson (Rev.) Olof H. Sylvan C. A. Greek (Cook) Unknown Challstrom Unknown Unknown Knud N Strarup and Alfred Sorenson Frank J. W. Johnson Darlene Rydell and sons Lance and Brandt Rydell Iola Delores Schneider Sturm Unknwon Schmidt, Unknown Goodenow Fred Brown, Barbara Brown J. Howard Abner, Inez Abner Mike Lewandowski, Kim Lewandowski (Nesler) Ed Cockrell, Leatha Cockrell, Beuford Cockrell Noisworthy, Gary D. Sherre Roy E. Terry, Frederick Terry J. D. Terry, F. Mildred Rev. George Alfred Shadwick, Rachel Pauline Shadwick John Glassnap, Stella Glassnap, Donald Bollinger, Raymond Bollinger Roy W. Friedrich Jr., Judith Friedrich Mr. Albert DeLay, Mrs. DeLay Lillian F. Parsons and Gwendolyn M. Parsons Garen K. Gdanian & Isabelle Gdanian Sarkis Sedefian & Marie E. Kizirian Timothy W. LaMountain & Helen J. LaMountain Dennis A. Rapp & Mary Grates Stoll Rapp Charles P. Green, Jr., M. Reges & R. Reges Green, Their Daughter George R. Brant, Jr. A. Brant, Sr. Roger and Kendra Pelham Karl A. Paulsen & Bertha J. Dittmann Paulsen Leo E. Godin III Alfred Godin Second Joseph J. Olander & Beverly A. Olander, His Wife Susan M. Thirolle & Jean-Claude Thirolle, her husband Jessica B. Arnold and Jake Fred Arnold, her husband

    into this: first_name middle_name last_name nickname title first_name_next_person middle_name_next_person last_name_next_person nickname_next_person title_next_person Olive Johnson William F. Hilyard Stanley Hajicek Grace Hajicek Phillip Felix Buteau Hazel M. Van Riper Mary Louise Van Riper Ditto Roberts Joyce Glenna Roberts Lester Norseth Mrs. Mae E. Mason Mrs. Jennie B. Mills Charles F. Mills Fred A. Bollman Phyllis M. Bollman A.M. Johnson G.Albert Johnson Olof H. Sylvan Rev. C. A. Greek (Cook) Unknown Challstrom Unknown Unknown Knud N. Starup Alfred Sorenson Frank J. W. Johnson Darlene Rydell Lance Rydell Iola Delores Schneider Sturm Unknwon Schmidt Unknown Goodenow Fred Brown Barbara Brown J. Howard Abner Inez Abner Mike Lewandowski Kim Lewandowski Nesler Ed Cockrell Leatha Cockrell Gary D. Noisworthy Sherre Roy E. Terry Frederick Terry J. D. Terry F. Mildred George Alfred Shadwick Rev. Rachel Pauline Shadwick John Glassnap Stella Glassnap Roy W. Friedrich Jr. Judith Friedrich Albert DeLay Mr. Unknown DeLay Mrs. Lillian F. Parsons Gwendolyn M. Parsons Garen K. Gdanian Isabelle Gdanian Sarkis Sedefian Marie Kizirian Timothy W. LaMountain Helen J. LaMountain Dennis A. Rapp Mary Grates Stoll Rapp Charles P. Green Jr. M. Reges Green George R. Brant Jr. A. Brant Sr. Roger Pelham Kendra Pelham Karl A. Paulsen Bertha J. Dittmann Paulsen Leo E. Godin III Alfred Godin Second Joseph J. Olander Beverly A. Olander Susan M. Thirolle Jean-Claude Thirolle Jessica B. Arnold Jake Fred Arnold

  4. Assistant

    The VBA code provided earlier can be modified to achieve the desired output. Please see the updated code below:

    Sub SplitNames()
        Dim rng As Range
        Dim cell As Range
        Dim NameArray() As String
        Dim i As Integer
        Dim OutputCol As Integer
    
        Set rng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        
        ' Add headers
        Range("B1").Value = "first_name"
        Range("C1").Value = "middle_name"
        Range("D1").Value = "last_name"
        Range("E1").Value = "nickname"
        Range("F1").Value = "title"
        Range("G1").Value = "first_name_next_person"
        Range("H1").Value = "middle_name_next_person"
        Range("I1").Value = "last_name_next_person"
        Range("J1").Value = "nickname_next_person"
        Range("K1").Value = "title_next_person"
    
        For Each cell In rng
            If cell.Value <> "" Then
                NameArray = SplitName(cell.Value)
                OutputCol = 2
                
                For i = LBound(NameArray) To UBound(NameArray) Step 5
                    cell.Offset(0, OutputCol).Value = NameArray(i)
                    cell.Offset(0, OutputCol + 1).Value = NameArray(i + 1)
                    cell.Offset(0, OutputCol + 2).Value = NameArray(i + 2)
                    cell.Offset(0, OutputCol + 3).Value = NameArray(i + 3)
                    cell.Offset(0, OutputCol + 4).Value = NameArray(i + 4)
                    
                    OutputCol = OutputCol + 5
                Next i
            End If
        Next cell
    
    End Sub
    
    Function SplitName(FullName As String) As Variant
        Const TitlePattern As String = "((Mr)|(Mrs)|(Ms)|(Rev)|(Dr)|(Sr)|(Jr))\."
        Const AndPattern As String = "(\s*&\s*|,?\s+and\s+|,?\s+&\s*|,\s+|;\s+)"
    
        Dim Names() As String
        Dim Titles() As String
        Dim i As Integer
        Dim OutputArray() As String
        Dim n As Integer
    
        Names = Split(Replace(FullName, "-", " "), " ")
        n = -1
        
        For i = LBound(Names) To UBound(Names)
            If Names(i) <> "" And Not IsNumeric(Names(i)) Then
                If Names(i) Like TitlePattern Then
                    ReDim Preserve OutputArray(n + 1)
                    OutputArray(n) = Names(i)
                ElseIf Names(i) = "(" Or Names(i) = ")" Then
                    ' Do nothing
                Else
                    If RegexMatch(Names(i), AndPattern) Then
                        n = n + 1
                    Else
                        ReDim Preserve OutputArray(n + 5)
                        
                        If OutputArray(n + 1) = "" Then
                            OutputArray(n + 1) = Names(i)
                        ElseIf OutputArray(n + 2) = "" Then
                            OutputArray(n + 2) = Names(i)
                        ElseIf OutputArray(n + 3) = "" Then
                            OutputArray(n + 3) = Names(i)
                        ElseIf OutputArray(n + 4) = "" Then
                            OutputArray(n + 4) = Names(i)
                        Else
                            OutputArray(n + 5) = Names(i)
                        End If
                    End If
                End If
            End If
        Next i
    
        SplitName = OutputArray
    End Function
    
    Function RegexMatch(ByVal StringToSearch As String, ByVal Pattern As String) As Boolean
        Dim Regex As Object
        Set Regex = CreateObject("VBScript.RegExp")
        With Regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = True
            .Pattern = Pattern
        End With
    
        RegexMatch = Regex.Test(StringToSearch)
    End Function
    

    Please note that this code may not cover all possible name variations and corner cases, but it should provide a good starting point for further customization if needed.

  5. User

    I have a code that concatenates values from columns A (last_name), B (first_name), C (middle_name), D (maiden_name), E (nickname) and F (title): =REGEXREPLACE( IF( ISBLANK(A2), , CONCATENATE( IF( ISBLANK(D2), CONCATENATE(A2, ", "), CONCATENATE(A2, " ", D2, ", ") ), B2, IF(ISBLANK(C2), , CONCATENATE(" ", C2)), IF( ISBLANK(E2), , IF( ISBLANK(C2), CONCATENATE(" (", E2, ")"), CONCATENATE(". (", E2, ")") ) ), IF( ISBLANK(F2), , IF( ISBLANK(C2), CONCATENATE(", ", F2), IF( ISBLANK(E2), CONCATENATE("., ", F2), CONCATENATE(", ", F2) ) ) ) ) ), "..", "." )

    write code that gets result of this function and returns values A (last_name), B (first_name), C (middle_name), D (maiden_name), E (nickname) and F (title)

  6. Assistant

    Below is a VBA code to retrieve the values A (last_name), B (first_name), C (middle_name), D (maiden_name), E (nickname) and F (title) based on the input generated using the given formula:

    Sub GetNameComponents()
        Dim rng As Range
        Dim cell As Range
        Dim NameArray() As String
    
        Set rng = Range("G1:G" & Cells(Rows.Count, 7).End(xlUp).Row)
    
        For Each cell In rng
            If cell.Value <> "" Then
                NameArray = SplitComponents(cell.Value)
                cell.Offset(0, 1).Value = NameArray(0)  ' last_name in column H
                cell.Offset(0, 2).Value = NameArray(1)  ' first_name in column I
                cell.Offset(0, 3).Value = NameArray(2)  ' middle_name in column J
                cell.Offset(0, 4).Value = NameArray(3)  ' maiden_name in column K
                cell.Offset(0, 5).Value = NameArray(4)  ' nickname in column L
                cell.Offset(0, 6).Value = NameArray(5)  ' title in column M
            End If
        Next cell
    End Sub
    
    Function SplitComponents(ByVal FullName As String) As Variant
        Dim last_name As String
        Dim first_name As String
        Dim middle_name As String
        Dim maiden_name As String
        Dim nickname As String
        Dim title As String
        
        last_name = Trim(Left(FullName, InStr(1, FullName, ",") - 1))
    
        Dim remaining As String
        remaining = Trim(Mid(FullName, InStr(1, FullName, ",")))
        
        If InStr(1, remaining, "(") > 0 Then
            nickname = Trim(Mid(remaining, InStr(1, remaining, "(") + 1))
            nickname = Trim(Left(nickname, InStr(1, nickname, ")") - 1))
            remaining = Trim(Left(remaining, InStr(1, remaining, "(") - 1))
        End If
        
        If InStr(1, remaining, ",") > 0 Then
            title = Trim(Mid(remaining, InStr(1, remaining, ",") + 1))
            remaining = Trim(Left(remaining, InStr(1, remaining, ",") - 1))
        End If
    
        Dim words() As String
        words = Split(remaining)
        
        first_name = words(0)
        
        If UBound(words) > 0 Then
            middle_name = words(1)
            
            If UBound(words) > 1 Then
                maiden_name = words(2)
            End If
        End If
        
        Dim result(5) As String
        result(0) = last_name
        result(1) = first_name
        result(2) = middle_name
        result(3) = maiden_name
        result(4) = nickname
        result(5) = title
        
        SplitComponents = result
    End Function
    

    In this code, we assume that the result of the formula is in column G. The GetNameComponents subroutine iterates over the cells in column G and calls the SplitComponents function, which processes the full name and extracts the required components. The components are then output in columns H to M.

    Please, replace the column index 7 with the index of the column which contains the results of the given formula.

  7. User

    error in line last_name = Trim(Left(FullName, InStr(1, FullName, ", ") - 1))

  8. Assistant

    Apologies for the mistake. Here's the updated line to fix the error:

    last_name = Trim(Left(FullName, InStr(1, FullName, ",") - 1))
    

    I have updated the line in SplitComponents function for you:

    Function SplitComponents(ByVal FullName As String) As Variant
        Dim last_name As String
        Dim first_name As String
        Dim middle_name As String
        Dim maiden_name As String
        Dim nickname As String
        Dim title As String
        
        last_name = Trim(Left(FullName, InStr(1, FullName, ",") - 1))
    
        Dim remaining As String
        remaining = Trim(Mid(FullName, InStr(1, FullName, ",") + 1))
        
        If InStr(1, remaining, "(") > 0 Then
            nickname = Trim(Mid(remaining, InStr(1, remaining, "(") + 1))
            nickname = Trim(Left(nickname, InStr(1, nickname, ")") - 1))
            remaining = Trim(Left(remaining, InStr(1, remaining, "(") - 1))
        End If
        
        If InStr(1, remaining, ",") > 0 Then
            title = Trim(Mid(remaining, InStr(1, remaining, ",") + 1))
            remaining = Trim(Left(remaining, InStr(1, remaining, ",") - 1))
        End If
    
        Dim words() As String
        words = Split(remaining)
        
        first_name = words(0)
        
        If UBound(words) > 0 Then
            middle_name = words(1)
            
            If UBound(words) > 1 Then
                maiden_name = words(2)
            End If
        End If
        
        Dim result(5) As String
        result(0) = last_name
        result(1) = first_name
        result(2) = middle_name
        result(3) = maiden_name
        result(4) = nickname
        result(5) = title
        
        SplitComponents = result
    End Function
    

    Now you can use the updated SplitComponents function in the main code to extract the name components.