I am working on Google Sheets since couple of years and found i very useful but now there is one situation i got faced and make me troubled to fix it.

In the current stage, when the line break is included in the cell value, when the cell is copied and pasted, it seems that it becomes """test""". In that case, when i want to copy and paste the cell values like "test" instead of """test""",

And there are some situations where i have added IF condition in formula which results comes sometimes in Line Break or sometimes in single line.

=CHAR(34)&SUBSTITUTE(A1,",",CHAR(10))&CHAR(34)

This formula works for Single Line string but when it comes to Line Break it adds more qoutation like"""test"""

I only want the solution like this even if its single line or Multiple line breaks always result should be "test".

your help will be much appreciated.

CodePudding user response：

this is a known issue and one way how to counter it is to copy the content of fx bar (or active cell) instead of the cell itself. you may also want to move down the output to not catch the formula itself. try:

={""; CHAR(34)&SUBSTITUTE(A1,",",CHAR(10))&CHAR(34)}

CodePudding user response：

This formula should output the expected value, even with several line returns and several " characters.

=CHAR(34)&SUBSTITUTE(A1,"""","")&CHAR(34)