Extract Names from long String with specific criteria?

[jeffcoleky]

Can someone help me create either a macro or a formula to extract the first 4 names out of each row in column "A". By "Help me create" I mean "Do it for me" because I'm absolutely lost as to where to begin...

The Raw data is all in column A and I want each name output to Columns B, C, D, E depending on how many are found. Any names after the 4th in any given row are not needed. I'd also like to re-order the name to be "First Last" instead of "LAST, FIRST, MIDDLE".

I've placed examples in this sheet of how I expect it will look when finished. If this request for help is unreasonable let me know and I'll try to find another way around this issue.

DOWNLOAD SAMPLE DATA BELOW HERE

Consistent Facts about the Raw Data (Column A):

1. If more than one name is contained in the string, each is separated by a semicolon
2. Name formats are "Last, First, Middle;" or "Last, First;"
3. Names will always have at least one comma
4. Company names will have at least one or more spaces separating the words
5. If a business name is longer than 20 characters long, a ", " is added to the string for each 20 characters

Macro or Formula should Ignore value if any of the following are true:

1. Charaters (including spaces) exceed 19 characters (because it must be a company name)
2. String contains "&" (because it must be a company name)
3. Duplicate to value already gathered from same row
4. String is after the 3rd semicolon (only need values for the first 4)

-Jeff


Excel 2013

	A	B	C	D	E
1		DESIRED OUTPUT
2	RAW DATA​	Name1	Name2	Name3	Name4
3	HART, JENNIFER, R; GENERAL REVENUE INC; COMMONWEALTH OF OHIO DIVIS, ION OF UNEMPLOYMENT, INSURANCE	Jennifer Hart
4	MILLER, JEANNIE, L; MILLER, JEANNIE; MILLER, JOHN, U; MILLER, JOHN; MORTON CENTER; COMMONWEALTH OF OHIO FINAN, CE & ADMINISTRATION, CABINET	Jeannie Miller	John Miller
5	DELACRUZ, CHRIS; DELACRUZ, CHRIS, B	Chris Delacruz
6	UNITED STATES OF AMERICA SECRE, TARY OF HOUSING & UR, BAN DEVELOPMENT; HARRELL, ESTELLE, C; HARRELL, DORSEY, E	Estelle Harrell	Dorsey Harrell
7	RAGAINS, SEBRINA, N; RAGAINS, RICHARD, C; RAGAINS, RICHARD; UNIVERSITY OF UROLOGY PLLC; TIPTON & UNROE PSC; MIDLANE PARK NEIGHBORHOOD ASSN, INC	Sabrina Ragains	Richard Ragains	University Of Urology Pllc
8	WILCHER, ANTHONY, L; REPUBLIC BANK & TRUST CO
9	SIMPSON, JACQUELINE
10	LE, SON, VAN; LE, THUYLIEU, THI	Son Le	Thi Thuylieu
11	SCRUGGS, ALMA, H; SCRUGGS, ALMA, JEAN; CITIFINANCIAL SERVICES INC; COMMERCIAL CREDIT CORP; UNION TRUST INC; ARROWOOD IDEMNITY CO; ROYAL INDEMNITY; CAPITAL ONE BANK USA NA; CAPITAL ONE BANK; ANDERSSON ANDERSSON COUNTY ME, TRO GOVERNMENT	Alma Scruggs	Citifinancial Services Inc	Citifinancial Services Inc
12	COLVIN, TODD; COLVIN, ERIC; BANK OF ANDERSSON & TRUST CO; THE ESTATE OF ROBERT WAYNE COLVIN	Todd Colvin	Eric Colvin
13	KNIGHT, NOLAN, A	Nolan Knight
14	TATE, MARIO, EXR; TATE, MARIO, A EXR; UNITED STATES OF AMERICA SECRE, TARY OF HOUSING & UR, BAN DEVELOPMENT; ANDERSSON ANDERSSON COUNTY ME, TRO DEPARTMENT OF CO, DES & REGULATIONS; CITIMORTGAGE INC; COMMONWEALTH OF OHIO CABIN, ET FOR HUMAN RESOURC, ES DIVISION OF UNEMP; BILARCZYK, NADINE	Mario Tate
15	SMITH, LORA, A; SMITH, LORA, ASHLEY; SMITH, ASHLEY; PROVIDIAN NATIONAL BANK; SMITH, JOSEPH; SMITH, JOSEPH, JR; SMITH, JOSEPH, L JR; BROWN, MICHAEL; NATIONAL CITY REAL ESTATE SERV, ICES LLC; NATIONAL CITY MORTGAGE CO; ARROW FINANCIAL SERVICES LLC; OEM COATINGS; AUTOVEST LLC; SMITH, MARTHA, EXR; ANDERSSON ANDERSSON COUNTY ME, TRO GOVERNMENT; CNAC; COMMONWEALTH OF OHIO WORKF, ORCE DEVELOPMENT CAB, INET EX REL DIVISION; EQUITABLE FINANCIAL SERVICES L, LC; CAPITAL ONE BANK USA NA; CAPITAL ONE BANK; ALBAN USED CARS I LLC; ALBAN USED CARS INC; FIRST RESOLUTION INVESTMENT CO, RP; COMMONWEALTH OF OHIO FINAN, CE & ADMINISTRATION, CABINET; BBC CHECK ADVANCE LLC; UNITED STATES OF AMERICA DEPAR, TMENT OF THE TREASUR, Y INTERNAL REVENUE S; NORTON HEALTHCARE INC; REPUBLIC BANK & TRUST CO; ECKART LLC; STONE CREEK FINANCIAL; ANDERSSON ANDERSSON COUNTY ME, TRO REVENUE COMMISSI, ON	Lora Smith	Ashley Smith
16	FOREE, MICHAEL, W JR; FOREE, MICHAEL, JR; FOREE, DEMITRIA, V; FOREE, DEMETRIA, V; FOREE, DEMITRIA; NORTON AUDUBON HOSPITAL; NORTON HEALTHCARE INC; DR GREGORY CECIL DMD LLC; ANDERSSON ANDERSSON COUNTY ME, TRO DEPARTMENT OF IN, SPECTIONS PERMITS &	Michael Foree	Demitria Foree
17	TRAN, NGOC; LE, HAJ, P; LE, HAI, P	Ngoc Tran	Haj Le
18	DEWALT, DAVID, J; DEWALT, KESHA, R; CHEMICAL MORTGAGE CO	David Dewalt	Kesha Dewalt	Mortgage Chemical
19	ABERNATHY, VELDA, J; COLVIN, WILMA, M; ABERNATHY, WILMA, M; TRILOGY HEALTHCARE OF JEFFERSO, N LLC; FRANCISCAN HEALTH CARE CENTER	Velda Abernathy	Wilma Colvin	Wilma Abernathy
20	ZACHARY, REGINA, C; B&B FUNDING LLC	Regina Zachary
21	FRENCH, STENNIS; FRENCH, REGINA; NATIONAL CITY MORTGAGE CO; SECRETARY OF HOUSING & URBAN D, EVELOPMENT; HOUSEHOLD FINANCE CORP II; CAPITAL ONE BANK	Stennis French	Regina French
22	CRAIG, JOHN, P JR; BLACK, LINDA; OHIO HOUSING CORP	John Craig	Linda Black
23	CLAY, PAUL; CLAY, PAUL, EUGENE III; CLAY, NANCY, G	Paul Clay	Nancy Clay
24	BREWER, CRAIG, L; BREWER, DONNA; BREYER, DONNA, Y; ANDERSSON ANDERSSON COUNTY ME, TRO GOVERNMENT; ANDERSSON ANDERSSON COUNTY ME, TRO DEPARTMENT OF CO, DES & REGULATIONS; CLINICAL PATHOLOGY ASSOCIATES, INC; CPA LABS; BARDSTOWN IMMEDIATE CARE CENTE, R	Craig Brewer	Donna Brewer
25	CRAWFORD, ROY; CRAWFORD, TRESSA; COMMONWEALTH OF OHIO EDUCA, TION CABINET EX REL, DIVISION OF UNEMPLOY; BILLTOWN FARM HOMEOWNERS ASSN, INC; DEUTSCHE BANK NATIONAL TRUST C, O, TR; FIRST FRANKLIN MORTGAGE LOAN T, RUST, TR	Roy Crawford	Tressa Crawford

<tbody>
</tbody>

Sheet1​

 

[Rick Rothstein]

Can you clarify a couple of points for us?

1) Why is there no output for Rows 8 and 9?

2) Why is the company name outputted in Message #18 (it is not a person's name because it does not have a comma within the first 20 characters)?

3) In Row 11, why doesn't the company name CITIFINANCIAL SERVICES INC have a comma after the last "E" in the word "SERVICES" (that is the position of the 20 character in the company name which is 26 characters long)?

 

[jeffcoleky]

Can you clarify a couple of points for us?

1) Why is there no output for Rows 8 and 9?

Mistake on my part, updated Sample Data Google Doc to add names.

2) Why is the company name outputted in Message #18 (it is not a person's name because it does not have a comma within the first 20 characters)?

Mistake on my part, updated Sample Data Google Doc to remove.

3) In Row 11, why doesn't the company name CITIFINANCIAL SERVICES INC have a comma after the last "E" in the word "SERVICES" (that is the position of the 20 character in the company name which is 26 characters long)?

I looked at a larger dataset and it appears estimate of 20 is incorrect. It's 30 characters will get a comma, NOT including the precluding " ". So " 123456789012345678901234567890" won't generally have a comma added.

So amend the rule: from 20 characters to 31 (which includes the space).

Sorry, i didn't realize i was so sloppy. should be fixed now.

 

[Rick Rothstein]

Mistake on my part, updated Sample Data Google Doc to remove.

When I click the link you posted in Message #1 for the sample data, the webpage tells me it is locked. You need to make it shared so we can download it.

 

[jeffcoleky]

Fixed, thanks for your attention to this for me!

 

[Rick Rothstein]

Fixed, thanks for your attention to this for me!

Got it. Just checking, this is what you have for Row 10...

LE, SON, VAN; LE, THUYLIEU, THI

You processed the names as "Son Le" and "Thi Thuylieu". Why isn't the second name "Thuylieu Le" instead?

Also, will your data always start on Row 3 (it is hard to tell if Row 1 is a real header or simply a description)?

Where did the name "Mario Tate" come from on Row 14?

 

[Rick Rothstein]

Got it. Just checking, this is what you have for Row 10...

LE, SON, VAN; LE, THUYLIEU, THI

You processed the names as "Son Le" and "Thi Thuylieu". Why isn't the second name "Thuylieu Le" instead?

Also, will your data always start on Row 3 (it is hard to tell if Row 1 is a real header or simply a description)?

Where did the name "Mario Tate" come from on Row 14?

One more question in addition to the above ones... can we make the assumption that if the text has at least one space without a comma in front of it, then it must be a company name and, as such, should not be copied as a result name? Seems like that is reasonable assumption given all of your names have a comma in front of each space in it.

 

[jeffcoleky]

One more question in addition to the above ones... can we make the assumption that if the text has at least one space without a comma in front of it, then it must be a company name and, as such, should not be copied as a result name? Seems like that is reasonable assumption given all of your names have a comma in front of each space in it.

Yes

 

[Rick Rothstein]

Yes

What about the questions in Message #6 ?

 

[jeffcoleky]

Got it. Just checking, this is what you have for Row 10...

LE, SON, VAN; LE, THUYLIEU, THI

You processed the names as "Son Le" and "Thi Thuylieu". Why isn't the second name "Thuylieu Le" instead?

Right, another of my mistakes. Corrected in Google Docs.

Also, will your data always start on Row 3 (it is hard to tell if Row 1 is a real header or simply a description)?

Yes

Where did the name "Mario Tate" come from on Row 14?

I don't understand why you question this one? It's the first name in row 14, why would it not be used? (In this instance, "Exr" Stands for "executor" as in "executor of an estate". So it's still a name we want)

"TATE, MARIO, EXR; TATE, MARIO, A EXR; UNITED STATES OF AMERICA SECRE, TARY OF HOUSING & UR, BAN DEVELOPMENT; ANDERSSON ANDERSSON COUNTY ME, TRO DEPARTMENT OF CO, DES & REGULATIONS; CITIMORTGAGE INC; COMMONWEALTH OF OHIO CABIN, ET FOR HUMAN RESOURC, ES DIVISION OF UNEMP; BILARCZYK, NADINE"