Here is my attempt. As PGC pointed out you really don't have any delimiters so the only row I could not break was the ALPHA-OMEGA Fraterinity. My option requires several helped columns to help keep the formulas relatively simple. Again, in the cases were you have addresses that don't have a comma to separate values then this won't work, so basically I'd say it works 90% of the time.
Bottom line with data inconsistencies like you have a simple or elegant solution will be tough.
The first 4 columns are to help break the text into its components then feed the individual parts.
Note that Address, City, State, and Zip are built after you have extracted various portions.
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Original</TD><TD>Address</TD><TD>City, State</TD><TD>Zip</TD><TD style="FONT-WEIGHT: bold">Name</TD><TD style="FONT-WEIGHT: bold">Street</TD><TD style="FONT-WEIGHT: bold">City</TD><TD style="FONT-WEIGHT: bold">State</TD><TD style="FONT-WEIGHT: bold">Zip</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>ABBOTT, J. SCOTT & BARBRA ANN 101 SUGARBEAR RD EATONTON, GA 31024 444-266-3244 266-7454</TD><TD>101 SUGARBEAR RD EATONTON, GA 31024 444-266-3244 266-7454</TD><TD>EATONTON, GA 31024 444-266-3244 266-7454</TD><TD>31024 444-266-3244 266-7454</TD><TD>ABBOTT, J. SCOTT & BARBRA ANN</TD><TD>101 SUGARBEAR RD </TD><TD>EATONTON</TD><TD>GA</TD><TD style="TEXT-ALIGN: right">31024 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>ADAMS, WANDA PINNACLE POINT 905 GREENPOND RD UNIT F-300 NORTH MACON, GA 31024</TD><TD>905 GREENPOND RD UNIT F-300 NORTH MACON, GA 31024</TD><TD>MACON, GA 31024</TD><TD style="TEXT-ALIGN: right">31024</TD><TD>ADAMS, WANDA PINNACLE POINT</TD><TD>905 GREENPOND RD UNIT F-300 NORTH </TD><TD>MACON</TD><TD>GA</TD><TD style="TEXT-ALIGN: right">31024</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>AGGIE, NEAL 666666 WESTYAWN CIRCLE APT 35476 MACON, GA 31210-9999 MANCHESTER NURSERY</TD><TD>666666 WESTYAWN CIRCLE APT 35476 MACON, GA 31210-9999 MANCHESTER NURSERY</TD><TD>MACON, GA 31210-9999 MANCHESTER NURSERY</TD><TD>31210-9999 MANCHESTER NURSERY</TD><TD>AGGIE, NEAL</TD><TD>666666 WESTYAWN CIRCLE APT 35476 </TD><TD>MACON</TD><TD>GA</TD><TD>31210-9999 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>ALPHA-OMEGA FRATERNITY 3191 SOPERTON HGWY EASTMAN GA 31023 TAX ID 300300100</TD><TD>3191 SOPERTON HGWY EASTMAN GA 31023 TAX ID 300300100</TD><TD>#VALUE!</TD><TD>#VALUE!</TD><TD>ALPHA-OMEGA FRATERNITY</TD><TD>#VALUE!</TD><TD>#VALUE!</TD><TD>#VALUE!</TD><TD>#VALUE!</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>ANDERSON, JENNI 4614 N. MAPLEWOOD DRIVE WARNER ROBINS, GA 34444 478-477-3899 960-7454</TD><TD>4614 N. MAPLEWOOD DRIVE WARNER ROBINS, GA 34444 478-477-3899 960-7454</TD><TD>ROBINS, GA 34444 478-477-3899 960-7454</TD><TD>34444 478-477-3899 960-7454</TD><TD>ANDERSON, JENNI</TD><TD>4614 N. MAPLEWOOD DRIVE WARNER </TD><TD>ROBINS</TD><TD>GA</TD><TD style="TEXT-ALIGN: right">34444 </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>
Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=SUBSTITUTE(A2,E2,"")</TD></TR><TR><TD>C2</TD><TD>=SUBSTITUTE(B2,F2,"")</TD></TR><TR><TD>D2</TD><TD>=SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(C2,",",""),G2&" ",""),H2&" ","")</TD></TR><TR><TD>E2</TD><TD>=MID(A2,1,MIN
(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890)-2))</TD></TR><TR><TD>F2</TD><TD>{=LEFT(B2,FIND
("|",SUBSTITUTE(B2," ","|",SUM(--(MID(B2,ROW(INDIRECT("$1:$"&FIND(",",B2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B2&1234567890))),TRUE)),1)=" ")))))}</TD></TR><TR><TD>G2</TD><TD>=LEFT(C2,FIND
(",",C2)-1)</TD></TR><TR><TD>H2</TD><TD>=MID(C2,FIND
(",",C2)+2,2)</TD></TR><TR><TD>I2</TD><TD>=IF(ISERROR
(LEFT(D2,FIND(" ",D2))),D2,LEFT
(D2,FIND(" ",D2)))</TD></TR><TR><TD>B3</TD><TD>=SUBSTITUTE(A3,E3,"")</TD></TR><TR><TD>C3</TD><TD>=SUBSTITUTE(B3,F3,"")</TD></TR><TR><TD>D3</TD><TD>=SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(C3,",",""),G3&" ",""),H3&" ","")</TD></TR><TR><TD>E3</TD><TD>=MID(A3,1,MIN
(FIND({1,2,3,4,5,6,7,8,9,0},A3&1234567890)-2))</TD></TR><TR><TD>F3</TD><TD>{=LEFT(B3,FIND
("|",SUBSTITUTE(B3," ","|",SUM(--(MID(B3,ROW(INDIRECT("$1:$"&FIND(",",B3,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B3&1234567890))),TRUE)),1)=" ")))))}</TD></TR><TR><TD>G3</TD><TD>=LEFT(C3,FIND
(",",C3)-1)</TD></TR><TR><TD>H3</TD><TD>=MID(C3,FIND
(",",C3)+2,2)</TD></TR><TR><TD>I3</TD><TD>=IF(ISERROR
(LEFT(D3,FIND(" ",D3))),D3,LEFT
(D3,FIND(" ",D3)))</TD></TR><TR><TD>B4</TD><TD>=SUBSTITUTE(A4,E4,"")</TD></TR><TR><TD>C4</TD><TD>=SUBSTITUTE(B4,F4,"")</TD></TR><TR><TD>D4</TD><TD>=SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(C4,",",""),G4&" ",""),H4&" ","")</TD></TR><TR><TD>E4</TD><TD>=MID(A4,1,MIN
(FIND({1,2,3,4,5,6,7,8,9,0},A4&1234567890)-2))</TD></TR><TR><TD>F4</TD><TD>{=LEFT(B4,FIND
("|",SUBSTITUTE(B4," ","|",SUM(--(MID(B4,ROW(INDIRECT("$1:$"&FIND(",",B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&1234567890))),TRUE)),1)=" ")))))}</TD></TR><TR><TD>G4</TD><TD>=LEFT(C4,FIND
(",",C4)-1)</TD></TR><TR><TD>H4</TD><TD>=MID(C4,FIND
(",",C4)+2,2)</TD></TR><TR><TD>I4</TD><TD>=IF(ISERROR
(LEFT(D4,FIND(" ",D4))),D4,LEFT
(D4,FIND(" ",D4)))</TD></TR><TR><TD>B5</TD><TD>=SUBSTITUTE(A5,E5,"")</TD></TR><TR><TD>C5</TD><TD>=SUBSTITUTE(B5,F5,"")</TD></TR><TR><TD>D5</TD><TD>=SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(C5,",",""),G5&" ",""),H5&" ","")</TD></TR><TR><TD>E5</TD><TD>=MID(A5,1,MIN
(FIND({1,2,3,4,5,6,7,8,9,0},A5&1234567890)-2))</TD></TR><TR><TD>F5</TD><TD>{=LEFT(B5,FIND
("|",SUBSTITUTE(B5," ","|",SUM(--(MID(B5,ROW(INDIRECT("$1:$"&FIND(",",B5,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B5&1234567890))),TRUE)),1)=" ")))))}</TD></TR><TR><TD>G5</TD><TD>=LEFT(C5,FIND
(",",C5)-1)</TD></TR><TR><TD>H5</TD><TD>=MID(C5,FIND
(",",C5)+2,2)</TD></TR><TR><TD>I5</TD><TD>=IF(ISERROR
(LEFT(D5,FIND(" ",D5))),D5,LEFT
(D5,FIND(" ",D5)))</TD></TR><TR><TD>B6</TD><TD>=SUBSTITUTE(A6,E6,"")</TD></TR><TR><TD>C6</TD><TD>=SUBSTITUTE(B6,F6,"")</TD></TR><TR><TD>D6</TD><TD>=SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(C6,",",""),G6&" ",""),H6&" ","")</TD></TR><TR><TD>E6</TD><TD>=MID(A6,1,MIN
(FIND({1,2,3,4,5,6,7,8,9,0},A6&1234567890)-2))</TD></TR><TR><TD>F6</TD><TD>{=LEFT(B6,FIND
("|",SUBSTITUTE(B6," ","|",SUM(--(MID(B6,ROW(INDIRECT("$1:$"&FIND(",",B6,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B6&1234567890))),TRUE)),1)=" ")))))}</TD></TR><TR><TD>G6</TD><TD>=LEFT(C6,FIND
(",",C6)-1)</TD></TR><TR><TD>H6</TD><TD>=MID(C6,FIND
(",",C6)+2,2)</TD></TR><TR><TD>I6</TD><TD>=IF(ISERROR
(LEFT(D6,FIND(" ",D6))),D6,LEFT
(D6,FIND(" ",D6)))</TD></TR></TBODY></TABLE></TD></TR><TR><TD>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>
