1

I have a couple of Google Spreadsheets I use to generate config files for things. The config file data ends up in a single cell, which is copied and pasted.

Problem is Google Sheets has this odd behavior where " marks get doubled into "" if there's a \n line break character, if it's a formula cell. If it's values, you can copy them right out of the cell editor without trouble.

for example a cell containing:

"foo"
"woo"

copies our of the cell editor as

"foo"
"woo"

but if a formula cell say ="""foo"""char(10)"""woo""" copies as

""foo""
""woo""

And if you try to copy it by editor, you get the formula. How to fix this?

Improve this question
1
  • Noticed it removed some line breaks. ""foo"" and ""woo"" should on separate lines. This makes the question more confusing. I edited the question to fix it
    – netsplit
    Oct 20, 2021 at 15:46

1 Answer 1

Reset to default
2

The key to the issue is it's formulas that produce the output. So what you need to do is find a way to make your output go to a cell containing only values. That sounds nuts, but there's actually a way to do it. Use arrayformula to write the output in a different cell.

=ARRAYFORMULA(if(row(A1:A2)=2,{your formula},"output")

put that in a1, it'll write the output to a2

=ARRAYFORMULA(if(row(A1:A2)=2,"""foo"""&char(10)&"""woo""","output")

if copied and pasted from a2 in edit mode, the result would be:

"foo"
"woo"

Note you have to double click the a2 to edit it, and then copy the text

Other suggestions I saw mentioned using char(13) for a line break, but that isn't a valid line break in some systems and breaks things.

Just wanted to put this on the internet because I didn't see it anywhere else, in the hopes it helps someone in a similar boat in the future.

Improve this answer
4
  • 2
    Well, yes. It's the double click the cell to edit it, and then copy the text bit that makes the difference, rather than whether the cell contains an array formula result or a literal value. When you copy a cell that contains a line break, you get quotes around the text, regardless of whether you are using an array formula. When you copy a value that contains a line break, you do not get quotes. To copy a value, open the cell for editing before copying. The value in a fill-down formula cell cannot be copied this way because you are then editing the formula rather than the value.
    – doubleunary
    Oct 20, 2021 at 17:01
  • Thanks I've updated the answer with that info to improve it
    – netsplit
    Oct 20, 2021 at 20:25
  • 1
    will it also work by selecting (in this case) cell A2 and then using Ctrl+C to copy the result to the clipboard?
    – graham
    Oct 21, 2021 at 9:26
  • That's exactly how it works. It writes the output to cell A2 as plain text. You have to click into A2 and select it all
    – netsplit
    Oct 24, 2021 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.