find & replace commas with newline on Google Spreadsheet

regex replace google-sheets
Dani picture Dani · Sep 23, 2011 · Viewed 54.7k times · Source

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

gmansour picture gmansour · Jul 11, 2012

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 you will be presented with this dialog

Related questions

Python string.replace regular expression

I have a parameter file of the form: parameter-name parameter-value Where the parameters may be in any order but there is only one parameter per line. I want to replace one parameter's parameter-value with a new value. I am using …

Read More
Replace specific characters within strings

I would like to remove specific characters from strings within a vector, similar to the Find and Replace feature in Excel. Here are the data I start with: group <- data.frame(c("12357e", "12575e", "197e18", "e18947") I start …

Read More
Java how to replace 2 or more spaces with single space in string and delete leading and trailing spaces

Looking for quick, simple way in Java to change this string " hello there " to something that looks like this "hello there" where I replace all those multiple spaces with a single space, except I also want the one or more …

Read More