Extract Fields (Name, Street Address, City, State, Zip) from a singular cell containing Addresses

[aeroballer]

Hello all,

I have a dataset that contains full addresses in a single cell. The addresses utilize 3 lines within the cell with the name or business in all CAPS.
The information contained in one cell is:
BOB REALTY LLC
123 N Main St
Salem, NH 03079

I would simply like to extract the information in this cell into 5 separate fields (Name, Street Address, City, State, Zip). Appreciate any help!

*Note, I have a formula that has worked for extracting Zip and State.
=MID(F2,LEN(F2)-7,2) yields State
=RIGHT(F2,5) yields Zip

 

[maabadi]

Please Upload Example with minimum 4-5 row. If you use XL2bb, it would be very better.

 

[aeroballer]

Please Upload Example with minimum 4-5 row. If you use XL2bb, it would be very better.

MMP ENTERPRISES LLC 4 Kerrygold Way Pittsford, NY 14534

RM BEACHFRONT LLC 1 Smokehill Dr STAMFORD, CT 06903

STEPHEN F LANG 105 John Browning Williamsburg, VA 23185

MAXFIELD, JOHN AARON & JODI POWLUS 13727 Foust Rd Conneaut Lake, PA 16316

COLE IRREVOCABLE TRUST 2342 W Genesee St Baldwinsville, NY 13027

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F
1
2	MMP ENTERPRISES LLC 4 Kerrygold Way Pittsford, NY 14534	MMP ENTERPRISES LLC	4 Kerrygold Way	Pittsford	NY	14534
3	RM BEACHFRONT LLC 1 Smokehill Dr STAMFORD, CT 06903	RM BEACHFRONT LLC	1 Smokehill Dr	STAMFORD	CT	06903
4	STEPHEN F LANG 105 John Browning Williamsburg, VA 23185	STEPHEN F LANG	105 John Browning	Williamsburg	VA	23185
Main

Cell Formulas
Range	Formula
B2:D4	B2	=TRIM(MID(SUBSTITUTE(LEFT($A2,LEN($A2)-10),CHAR(10),REPT(" ",100)),COLUMNS($B$2:B2)*100-99,100))
E2:E4	E2	=MID(A2,LEN(A2)-7,2)
F2:F4	F2	=RIGHT(A2,5)

 

[aeroballer]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F
1
2	MMP ENTERPRISES LLC 4 Kerrygold Way Pittsford, NY 14534	MMP ENTERPRISES LLC	4 Kerrygold Way	Pittsford	NY	14534
3	RM BEACHFRONT LLC 1 Smokehill Dr STAMFORD, CT 06903	RM BEACHFRONT LLC	1 Smokehill Dr	STAMFORD	CT	06903
4	STEPHEN F LANG 105 John Browning Williamsburg, VA 23185	STEPHEN F LANG	105 John Browning	Williamsburg	VA	23185
Main

Cell Formulas
Range	Formula
B2:D4	B2	=TRIM(MID(SUBSTITUTE(LEFT($A2,LEN($A2)-10),CHAR(10),REPT(" ",100)),COLUMNS($B$2:B2)*100-99,100))
E2:E4	E2	=MID(A2,LEN(A2)-7,2)
F2:F4	F2	=RIGHT(A2,5)

Thank you! This works quite well. It works for nearly all of my dataset, a few of the addresses do not follow the exact format in my example. Thank you very much.

 

[Fluff]

You're welcome & thanks for the feedback.