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