Break multiple lines in cells into one column

[excel_alex]

Hi,

I have a spreadsheet with a list of data. Most rows have multiple lines of text. On the screenshot below I've selected one row and you can see at the top the full data in the row:

I've tried text to columns and using Ctrl+J but that doesn't work.

I wanted to attach the spreadsheet so you could see what it's like but I couldn't see a way to attach it to the post.

Any ideas?

 

[nick323f]

I see that your data import has put data into the cell which is appears to contain carriage returns.
is this formula helpful?
=SUBSTITUTE(A1,CHAR(10),",")

it will replace the carriage return with a comma after which you can do a text to column

 

[Fluff]

If the text to columns didn't work with Ctrl J you might want to replace CHAR(10) in the above formula with CHAR(13)

 

[excel_alex]

Thank you. Both char 10 and 13 seem to produce the same output.

Here's what I'm seeing now when I try to do text to columns

Firstly do I need to then copy this output and paste only values so that text to columns doesn't think I want to do something with the formula.

I tried doing that and it didn't work with comma selected as the delimiter. Probably because there's two commas between each.

FYI for the end result I'd like to do is have all the data put into one column on separate rows.