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

aeroballer

New Member
Joined
Jul 1, 2005
Messages
24
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Please Upload Example with minimum 4-5 row. If you use XL2bb, it would be very better.
 
Upvote 0
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
 
Upvote 0
How about
+Fluff v2.xlsm
ABCDEF
1
2MMP ENTERPRISES LLC 4 Kerrygold Way Pittsford, NY 14534MMP ENTERPRISES LLC4 Kerrygold WayPittsfordNY14534
3RM BEACHFRONT LLC 1 Smokehill Dr STAMFORD, CT 06903RM BEACHFRONT LLC1 Smokehill DrSTAMFORDCT06903
4STEPHEN F LANG 105 John Browning Williamsburg, VA 23185STEPHEN F LANG105 John BrowningWilliamsburgVA23185
Main
Cell Formulas
RangeFormula
B2:D4B2=TRIM(MID(SUBSTITUTE(LEFT($A2,LEN($A2)-10),CHAR(10),REPT(" ",100)),COLUMNS($B$2:B2)*100-99,100))
E2:E4E2=MID(A2,LEN(A2)-7,2)
F2:F4F2=RIGHT(A2,5)
 
Upvote 0
Solution
How about
+Fluff v2.xlsm
ABCDEF
1
2MMP ENTERPRISES LLC 4 Kerrygold Way Pittsford, NY 14534MMP ENTERPRISES LLC4 Kerrygold WayPittsfordNY14534
3RM BEACHFRONT LLC 1 Smokehill Dr STAMFORD, CT 06903RM BEACHFRONT LLC1 Smokehill DrSTAMFORDCT06903
4STEPHEN F LANG 105 John Browning Williamsburg, VA 23185STEPHEN F LANG105 John BrowningWilliamsburgVA23185
Main
Cell Formulas
RangeFormula
B2:D4B2=TRIM(MID(SUBSTITUTE(LEFT($A2,LEN($A2)-10),CHAR(10),REPT(" ",100)),COLUMNS($B$2:B2)*100-99,100))
E2:E4E2=MID(A2,LEN(A2)-7,2)
F2:F4F2=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.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top