find & replace commas with newline on Google Sprea

问题:

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

回答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

回答2:

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)

回答3:

Using the substitute formula gmansour mentioned above works in the new google sheets. However, doing a Find and Replace (CTRL H) does not. I tried using char(10) as a replace and only shows "char(10)".

回答4:

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.

回答5:

To change all ">"(greater than) to ">\n"(greater than followed by a newline), I took this 3 step circuitous approach.

1. Copy the text onto LibreOffice "Text document"
2. Edit => Find & Replace // shortcut: Ctrl + H
3. Fill out 3 fields(boxes 1~3 in the below figure) there and click "Replace All" button.

回答6:

example using formula to parse

=SPLIT(A14,CHAR(10))

回答7:

Use the "replace using regular expressions" option. Then the target is \n and the replacement is , (i.e., comma followed by space).