splitting a cell that has many values separated by a carriage return

[Nerpilis]

Back again cause I'm stumped: I have many many tables that have data in a single cell separated by a carriage return. I would eventually like each of those 'row entries' to have their own cell. See attached file for before/after clarification. I think it will have something to do with the CHAR(10) and find/search combos but I'm not sure how to do this for as many 'row' entries as my data has.

Temporarily i have found that if I highlight/copy the values in the cell then paste into a txt file I can then import back into Excel with the values separated the way I want, I just think that there's got to be a way with fewer steps for the process. Any help/insight appreciated! split cell into many rows.xlsx

[martindwilson]

copy paste special into word as unformatted text
find ^p replace with ^|
copy paste back into excel
essentially the same but didnt work when i used a text file

or text to columns delimited choose other enter ctrl+j that will put then across columns then copy paste special transpose
other than that you'll need code to do it

[Nerpilis]

Thanks for the help! I'll have to play around with the ctrl+J never knew you could do that...and maybe record a macro. However it's looking like I'll have to save to a file then import.