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
HART, JENNIFER, R; GENERAL REVENUE INC; COMMONWEALTH OF OHIO DIVIS, ION OF UNEMPLOYMENT, INSURANCE	Jennifer Hart
MILLER, JEANNIE, L; MILLER, JEANNIE; MILLER, JOHN, U; MILLER, JOHN; MORTON CENTER; COMMONWEALTH OF OHIO FINAN, CE & ADMINISTRATION, CABINET	Jeannie Miller	John Miller
DELACRUZ, CHRIS; DELACRUZ, CHRIS, B	Chris Delacruz
UNITED STATES OF AMERICA SECRE, TARY OF HOUSING & UR, BAN DEVELOPMENT; HARRELL, ESTELLE, C; HARRELL, DORSEY, E	Estelle Harrell	Dorsey Harrell
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
WILCHER, ANTHONY, L; REPUBLIC BANK & TRUST CO
SIMPSON, JACQUELINE
LE, SON, VAN; LE, THUYLIEU, THI	Son Le	Thi Thuylieu
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
COLVIN, TODD; COLVIN, ERIC; BANK OF ANDERSSON & TRUST CO; THE ESTATE OF ROBERT WAYNE COLVIN	Todd Colvin	Eric Colvin
KNIGHT, NOLAN, A	Nolan Knight
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
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
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
TRAN, NGOC; LE, HAJ, P; LE, HAI, P	Ngoc Tran	Haj Le
DEWALT, DAVID, J; DEWALT, KESHA, R; CHEMICAL MORTGAGE CO	David Dewalt	Kesha Dewalt	Mortgage Chemical
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
ZACHARY, REGINA, C; B&B FUNDING LLC	Regina Zachary
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
CRAIG, JOHN, P JR; BLACK, LINDA; OHIO HOUSING CORP	John Craig	Linda Black
CLAY, PAUL; CLAY, PAUL, EUGENE III; CLAY, NANCY, G	Paul Clay	Nancy Clay
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
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>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"] DESIRED OUTPUT [/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]

RAW DATA​

[/TD]
[TD="bgcolor: #E2EFDA, align: center"]Name1[/TD]
[TD="bgcolor: #E2EFDA, align: center"]Name2[/TD]
[TD="bgcolor: #E2EFDA, align: center"]Name3[/TD]
[TD="bgcolor: #E2EFDA, align: center"]Name4[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]

</tbody>

Sheet1​

 

[Rick Rothstein]

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"

My fault... I turned word wrap on and then shrunk the width of Column A and changed the height to show two rows thinking that was enough, but Row 14 is three lines high and I didn't see the first line of it.

However, while I am looking at Row 14, just double checking... the name at the end doesn't show because it is past the 4th semi-colon? Or is it that once you get a company name, you stop looking?

 

[jeffcoleky]

My fault... I turned word wrap on and then shrunk the width of Column A and changed the height to show two rows thinking that was enough, but Row 14 is three lines high and I didn't see the first line of it.

However, while I am looking at Row 14, just double checking... the name at the end doesn't show because it is past the 4th semi-colon? Or is it that once you get a company name, you stop looking?

Sometimes a company will be the leading entry so we can't exclude everything after the first company name or we exclude the important names. Thus the 4th semicolon is the rule.

 

[Rick Rothstein]

Sometimes a company will be the leading entry so we can't exclude everything after the first company name or we exclude the important names. Thus the 4th semicolon is the rule.

See if this macro does what you want...

Sub GetNamesFromData()
  Dim R As Long, C As Long, X As Long, Z As Long, IsThere As Long
  Dim Data As Variant, Results As Variant
  Dim Temp As String, Txt() As String, S() As String
  Data = Range("A3", Cells(Rows.Count, "A").End(xlUp))
  ReDim Results(1 To UBound(Data), 1 To 4)
  For R = 1 To UBound(Data)
    C = 0
    Txt = Split(Data(R, 1) & ";;;;", ";")
    For X = 0 To 3
      If Txt(X) Like "*, *" And Not Txt(X) Like "*[!,] *" Then
        C = C + 1
        IsThere = 0
        S = Split(Txt(X) & ", , ", ", ")
        Temp = Trim(S(1)) & " " & Trim(S(0))
        For Z = 1 To C - 1
          If Results(R, Z) = Temp Then IsThere = 1
        Next
        If IsThere = 0 Then
          Results(R, C) = StrConv(Temp, vbProperCase)
        Else
          C = C - 1
        End If
      End If
    Next
  Next
  Range("B3").Resize(UBound(Results), 4) = Results
End Sub

NOTE: Your output for Row 16 shows two names ("Michael Foree" and "Demitria Foree"), but my code shows three names... the second name is in there twice, but the first name is spelled differently... I cannot think of a way to make the code decide these are the same name without having a "dictionary" of all the possible misspellings of all the possible names.

 

[jeffcoleky]

See if this macro does what you want...

Sub GetNamesFromData()
  Dim R As Long, C As Long, X As Long, Z As Long, IsThere As Long
  Dim Data As Variant, Results As Variant
  Dim Temp As String, Txt() As String, S() As String
  Data = Range("A3", Cells(Rows.Count, "A").End(xlUp))
  ReDim Results(1 To UBound(Data), 1 To 4)
  For R = 1 To UBound(Data)
    C = 0
    Txt = Split(Data(R, 1) & ";;;;", ";")
    For X = 0 To 3
      If Txt(X) Like "*, *" And Not Txt(X) Like "*[!,] *" Then
        C = C + 1
        IsThere = 0
        S = Split(Txt(X) & ", , ", ", ")
        Temp = Trim(S(1)) & " " & Trim(S(0))
        For Z = 1 To C - 1
          If Results(R, Z) = Temp Then IsThere = 1
        Next
        If IsThere = 0 Then
          Results(R, C) = StrConv(Temp, vbProperCase)
        Else
          C = C - 1
        End If
      End If
    Next
  Next
  Range("B3").Resize(UBound(Results), 4) = Results
End Sub

Wowzers! it worked! Yay. Thanks so much for that help! Having both names come up with a small typo is a good thing actually. thanks again! I could have never done it without your help.