How to split text to columns from newline

[luvirini]

I have cells that contains a newline in middle, as in the cell contents is:
“Text1
Text2”

I want to split it to: “Text1” and “Text2”

Text to columns tool does not seem to have an option to split by newline/linebreak.

Is there an another way to do it?

[Vanadium]

One way would be to replace the newline in the cells by a tab, which then can be used to split the text using the Data → Text to Columns command.

1. Select the cells
2. Invoke Search and Replace. Check Regular expressions, then, in Find: type \n (this stands for a new line character in regular expressions), and for Replace:, provide \t, the Tab character.
3. Click Replace All and close the dialog.
4. The cells now can be converted to multiple columns based on the “Tab” character.

[luvirini]

Thanks, that was a great idea.

[Aqhim]

This really worked for me, thank you!