find & replace commas with newline on Google Spreadsheet

Question

I've been trying to figure out how to replace commas with line-breaks. I've tried using (/r/n/), char(10), the Unicode representation (can't remember now), but it rather predictably replaces the comma with whatever I type in the other box, not converting it to a line-break

Answer 1

IF you are trying to use the Ctrl+F to find and replace well you can't. at least I dont know how.

but if you have a cell say A1 that contains the following : "stack,overflow" then if you type this in A2 it should work: =SUBSTITUTE(A1, ",", char(10))

https://support.google.com/docs/bin/static.py?hl=en&topic=25273&page=table.cs

you can then copy the content of A2 and in A3 right click -> Paste Special... -> Values Only.

Hope this helps

UPDATE 2016-AUG Google Spreadsheet now support searching for newline using \n here is how: Edit -> Find and Replace

Answer 2

If I properly understood you want to obtain something like that:

The trick I discovered is this one:

1. you insert an expression as =SUBSTITUTE(A3,",","")
2. then edit the formula you've just types, you placed the cursor in between the latest two double quotes: =SUBSTITUTE(A3,",","<PLACE CURSOR HERE>")
3. and finally you press CTRL+ENTER. This is the way to insert a "new line" character.

Answer 3

This response doesn't specifically answer the original question, but it is a similar use-case that I found which may help someone else. If you are searching for a newline character with other text and you want your replace to keep the newline character, here's one way you can do it.

My example searches for newline plus space and then replaces it with just a new line.

Find: (\n)\s

Replace: $1

It would be nice if google allowed regex in the replace field, but this might help someone if the newline character is part of the search parameters and needs to be included in the replace portion.

NOTE: Per Google's documentation, replace groups only work with Sheets. (as of July 13, 2017)