Multiple lines in one cell to multiple cells

[tc113]

I have a column of cells with Customer info and address in each row, that I need to move into multiple columns. ie Cust name, acct #,address, city,state, zip the problem is ALL of the info for each customer is in ONE cell.To be clear, cell B1 has complete name address etc for one customer, B2 has the info for another, B3... on and on. To complicate matters as in the example, there is not a pattern in the lines. the first example shows the info beginning on the 2nd line of the cell, but the 2nd example shows info in the first line.
I have tried the text to column fixed width. Didn't work because names and addresses are of varying lengths. Then I tried delimited, but there isn't a common separation between different data. I am using excel 2007. I am NOT an excel expert, so be gentle...
FYI I did go through many many threads and found some that were close to my problem but not close enough to work. Thanks for your time.

"
JOES TOWING SERVICE [0123451]
123 STREET

CALEDONIA FL 12345
"
"BUBBA / SHOP
ACCURATE AUTO SUPPLY INC [0234567]
21234 HYDRAULIC DR

LOCKWOOD OR 45678
"

 

[jeremypyle]

I have a very simple method:

Select all the cells in the column that you have. If for example, you have content in cells a1 to a500, Select all these cells. Press Ctrl C, then open another sheet and in a1, right click and select "Paste special" then select the "Transpose" option and click "ok"

It will now transpose horizontally along the page. This won't yet solve the issue of the data being all in one cell.

Now select all the cells that are filled in the 1 row (or perhaps more than 1 row if more than 1 row show).

Copy these and paste them into either Notepad or Textpad.

Now Ctrl A to select everything in the notepad or textpad file. Copy this and paste it back into a new excel sheet. Now they should be in several lines. Does this help?

 

[Michael M]

Maybe the data is separated by Non breaking spaces or Line Breaks
Try
Text to Columns
Delimited and in the Other field type Ctrl+J ( it looks like nothing has been inserted)... the table will show your data split apart, simply hit the Finish button to lock it in.

 

[tc113]

Jeremy
It kind of worked but it put the info in separate rows. I need it in separate columns.
Thanks anyway.

 

[tc113]

Micheal,
It worked perfectly!
Thanks!

 

[Michael M]

Glad you got it sorted.....

 

[jeremypyle]

haha, yes Michael is the master of excel. sorry about putting you down the wrong track

 

[Michael M]

Hey Jeremy
Multiple answers will cover ALL possibilities.......I got lucky this time, maybe next time the OP will need your answer.....