Separating Names from Single to Multiple Columns

[ProvPC]

I have an Excel spreadsheet containing roughly 3500 rows. The data from these rows needs to be copied into various tables in an Access database - but first, I need to manipulate the data a wee bit to fit the table structure in the database. There are a couple of things I'll need to get sorted out, but here's the first:

Column B in the spreadsheet contains a list of complete names of people, along with spouse names, in a format similar to Mouse, Mickey & Minnie. The good news is that the format is pretty consistent with the placement of commas and ampersands. The bad news is that the names themselves are formatted in a variety of ways. Some examples include:

Mouse, Mickey & Minnie
Dog, Pluto
Duck, Donald Douglas & Daisy
Dawg, Goofy & Clarabelle Mae Cow
Cat, Sylvester & Tweety Bird (Tricky, in the sense that "Bird" could be Tweety's last name, or Mrs. Cat's middle name. I'll have to check these manually)

The trouble is that the database table requires that the names be separated out into Head of House First Name, HoH Middle Initial, HoH Last Name, Spouse First Name, Spouse Middle Initial, and Spouse Last Name.

Though the vast majority of the data is represented by the first two examples above, there are enough variances to make manually fixing them somewhat of a chore. What I'm hoping for is a set of formulas that can look at column B, and extract from it the appropriate information required to fill in other columns that I'll create for that purpose. Here's my (very, very rough) idea of what should go where:

Column B (Complete Name) - the existing column with the data as it currently exists
Column C (Head of House Last Name) - the word before the comma
Column D (Head of House First Name) - the word after the comma (Bonus points if any Jr or Sr present can be placed in this column as well!)
Column E (Head of House Middle Initial) - the second word after the comma, but before the ampersand. Empty if none exists.
Column F (Spouse First Name) - the first word after the ampersand. Empty if none exists.
Column G (Spouse Middle Initial) - the second word after the ampersand. Empty if none exists.
Column H (Spouses Last Name) - the third word after the ampersand. Empty if column F is empty. Otherwise, the contents of Column C.

I tried doing this myself as a series a steps using the information found in another thread as a guide (thanks lancerj017). but I was only marginally successful. I was able to accomplish Column C, but column D didn't work if there wasn't a spouse, and column F would sometimes show the "&" and sometimes not, though I could not determine any difference in either the original data or the formula I was using (which was simply copied down the column).

Any help would be greatly appreciated!

 

[tygrrboi]

Does this work?

Sub SeparateNames()Dim Names As Variant
Dim NameParts As Variant
Dim UnSorted As String, MiddleInitials As String
Dim lastRow As Integer, i As Integer, j As Integer


With ActiveSheet
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    
    For i = 1 To lastRow
        UnSorted = .Range("B" & i).Value
        UnSorted = Replace(UnSorted, ", ", " & ")

        Names = Split(UnSorted, " & ")
        UnSorted = vbNullString
        .Range("C" & i).Value = Names(0)
        NameParts = Split(Names(1), " ")
        .Range("D" & i).Value = NameParts(0)
        If UBound(NameParts) > 0 Then
            For j = 1 To UBound(NameParts)
                MiddleInitials = MiddleInitials & Left(NameParts(j), 1) & ". "
            Next j
            .Range("E" & i).Value = Left(MiddleInitials, Len(MiddleInitials) - 1)
            MiddleInitials = vbNullString
        End If
        Erase NameParts
        If UBound(Names) = 2 Then
            NameParts = Split(Names(2), " ")
            .Range("F" & i).Value = NameParts(0)
            If UBound(NameParts) > 0 Then
                For j = 1 To UBound(NameParts)
                    MiddleInitials = MiddleInitials & Left(NameParts(j), 1) & ". "
                Next j
                .Range("G" & i).Value = Left(MiddleInitials, Len(MiddleInitials) - 1)
                MiddleInitials = vbNullString
            End If
            Erase NameParts
            .Range("H" & i).Value = Names(0)
        End If
        Erase Names
    Next i
End With




End Sub

 

[ProvPC]

Hi there! Thanks for replying!

Gosh tygrrboi - that certainly looks impressive! I'm almost certain it would work great, but I'm afraid I have no clue how to use it in Excel ( I'm certainly willing to learn though!
I tried entering it as VBS code in a Macro, but it generated an error (most likely because I'm not doing it correctly - or am misunderstanding what I'm supposed to be doing with it in the first place. I'm sorry.

You actually took me by surprise. I was expecting some sort of formula to enter at the top of each column to copy the relevant part of the data in Column B to that column. I'd love to give your approach a try, if you can teach me how to use it.

 

[tygrrboi]

Looking at the formatting of the code on this message board... it might be an error without the line return after Sub SeparateNames()

Could you try hitting enter to put "Dim Names as Variant" on a separate line?

 

[tygrrboi]

If that doesn't work, perhaps it could be done with some helper columns... I tried doing it as a formula earlier without the helper columns but was getting seriously bogged down with the length of the formula. I might give it a try again tomorrow (it's late evening where I am now) and see if I could make some headway with it.

I did have a question. Would there ever be a person with two middle names? Like Mike R. D. Smith?

 

[István Hirsch]

Please show us a spreadsheet with the expected results for the examples given.

 

[tygrrboi]

It might help, but I have uploaded a file with my code above and your examples to help you see how it works. You can add more names below the example ones, or even new names instead of the ones I have there.

 

[ProvPC]

Looking at the formatting of the code on this message board... it might be an error without the line return after Sub SeparateNames()

Could you try hitting enter to put "Dim Names as Variant" on a separate line?

I'll give that a try and let you know how it goes. That's about where the debug highlighted the text, so you're probably exactly right!

 

[ProvPC]

I did have a question. Would there ever be a person with two middle names? Like Mike R. D. Smith?

I looked over the spreadsheet and didn't notice any with two middle names - but there are a LOT of names and I could easily have missed it. The database allows for two middle initials, but I didn't spot any in the data on hand.
I did, however, notice a bit of an anomaly that I hadn't counted on, that may complicate things a bit. There are some users with what appear to be two-word last names (like "de Santiago" or "La Barbara"). I'm not sure there's any way to account for that, but it's not an overwhelming number of entries, so it's probably best handled by my searching through and tidying up afterwards.

 

[ProvPC]

Please show us a spreadsheet with the expected results for the examples given.

Sure! Thanks for your interest! Here's what I have in mind.