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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please Upload Example with minimum 4-5 row. If you use XL2bb, it would be very better.
 

aeroballer

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

aeroballer

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,846
Messages
5,627,235
Members
416,232
Latest member
Ash1432

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
Top