Data in multiple rows in one cell

Welsh Mark3

Board Regular
Joined
Apr 7, 2014
Messages
164
Office Version
  1. 365
I have a file of data where the company name and address are in a single cell over multiple rows. Does anyone have a recommendation to separate each line into its own column?

Line 1 = Company Name
Line 2 = Address
Line 3 = City, State & Zip

AMERICAN BILLIARDS AND OUTDOOR RECREATIO

312 LEE ST WEST

CHARLESTON, WV 25302 USA
AMERICAN POOL AND SPA

3237 "A" E SUNSHINE

SPRINGFIELD, MO 65804 USA
APOLLO POOLS AND SPAS

8 HARDING ST

LAKEVILLE, MA 02347 USA
APPLIANCES AND MORE

2036 MAIN ST

BAKER CITY, OR 97814 USA
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Try...
Using Text to Columns. Apply Delimited. Activate "Treat consecutive delimiters as one."
Using Other character enter a carriage return with the key stroke ALT + 0010
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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