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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
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,546
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,546
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,570
Messages
5,625,579
Members
416,119
Latest member
JCLLE

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