24

This may be the most niche question ever but let's try it anyway.

I have a Google Sheets spreadsheet that contains cells with multiple lines of text. Each line of text is separated by a soft break.

As shown below, when I copy the contents of a cell (row 2 in the screenshot) from the Google Sheets app to the Instagram caption box, a quote mark is added to the beginning and end of the caption.

Sample spreadsheet data

Instagram caption

If I copy the contents of a cell and that cell has only a single line of text (row 3 in the screenshot), no quote mark is added.

enter image description here

I am using an iPhone 11 running the latest version of iOS.

player0
  • 124,011
  • 12
  • 67
  • 124
zgall1
  • 2,865
  • 5
  • 23
  • 39
  • my hunch is that it could be because the line break characters are actually vertical tabs or some other strange or incompatible character, and the quotes are the "easiest" way to avoid further complications. You might be able to overcome this by replacing the line break chars with actual newlines using a formula. – varontron Jan 23 '20 at 20:31
  • Can you elaborate on what you mean by "newlines using a formula"? – zgall1 Jan 23 '20 at 21:11
  • You can use the `char` function to embed newlines programmatically, e.g., `="Test 1"&char(10)&"Test 2"` That said, I'm not sure it will eliminate the quoting issue but worth a try – varontron Jan 23 '20 at 21:52
  • That didn't work. – zgall1 Jan 24 '20 at 03:28
  • 1
    Following varaontron’s suggestion, you can use `char(13)` instead of `char(10)`. Copy & paste will then work with the linefeeds showing up in the copied text (and no extra quote marks), but the linefeeds won’t be visible in Google Sheets itself. – Mischinab Aug 25 '21 at 15:37

5 Answers5

25

The extra quotes are added when there are special characters in the cell. In your scenario, the Line Feed characters are causing this. Definitely annoying.

There is a way around this – using Carriage Return characters, rather than Line Feeds to separate each line. For some reason these characters don’t cause the quotation marks to appear.

One thing you can do in your sheet is to create a helper cell that will take your text, and replace the line feeds with carriage returns (assuming your input text is in cell A2, add this formula to an empty cell):

=SUBSTITUTE(A2,char(10),char(13))

The output for this will look like it doesn’t contain linefeeds, but when you copy & paste from that cell, the linefeeds will be there in the pasted text, without the extra quotation marks.

Mischinab
  • 2,751
  • 1
  • 20
  • 15
  • 1
    That SUBSTITUTE formula worked like a charm for me… much easier than some of the other solutions that require some intermediate transfer of the data. You can copy directly from the cell and paste to the target location and, voila, no quotation marks. – Vincent Dec 04 '21 at 21:50
  • This works fine when pasting into Notepad on Windows 11, but when I attempted to paste it into Facebook Messenger (web), running in both the Brave browser and Microsoft Edge, it came out all strung together as a single paragraph of text. The only way I was able to make this work, was to highlight the cell, switch focus to the text input bar, `Ctrl+A` to select all, `Ctrl+C` to copy, then `Esc` to exit text entry without committing the edits. Not exactly efficient, but it works. – Todd Powers Jul 04 '23 at 19:03
10

The quotations are inserted by the target application when non-printable or otherwise incompatible characters appear in the copied text. There are several scenarios in which the quotes don't appear, and several in which they do.

For example in the MacOS Notes application, consider a cell containing either a vertical tab (appearing as a line break in a single cell with a Cmd-Enter on Mac or Cntl-Enter on Windows) or a newline character in a formula such as ="test"&char(10)&"test". When copied and pasted into a record in Notes, the text is copied as is (i.e., as expected). However when pasting into the Notes search box, the quotes appear, such as described in the question.

There appear to be 3 alternative ways to handle this issue:

Strip the non-printable characters with a formula

Using the CLEAN function, the characters will be stripped. This will produce oft undesirable results, but will eliminate the quotes. See the Wrapped in CLEAN column:

enter image description here

Paste elsewhere first

In the Notes example, one can paste the offending text into a Note (or presumably any text editor.) The offending quotes are omitted. The text can then be recopied and repasted without quotes. This will still collapse a line feed into a space:

enter image description here

Publish to Web and copy from there

Publishing a sheet with non-printable characters enables quoteless copy, like the previous option, but may be a preferable. See the test sheet

Copy from the Sample Text column. You can paste without quotes, but the line break is stripped and replaced with a space as above.

varontron
  • 1,120
  • 7
  • 21
  • 3
    I don't think this is right. When you copy multiline text from a cell in a Google sheet, multiple versions are placed in the clipboard: the formula itself, the text in HTML form, the text in rich text format and the text in plain text format. For some reason, Google includes quotes in the plain text format (probably to assist in copying and pasting data between apps). If you paste into an environment that accepts rich text or HTML, you get one of those versions, with no quotes. If you paste into an environment that only accepts plain text, you get the plain text version, which includes quotes. – Matthias Fripp Sep 13 '20 at 23:33
  • 1
    @MatthiasFripp Consider adding it as a answer. – TheMaster Sep 26 '20 at 21:36
  • When I paste to notes or TextEdit it omits the quotes, but when I paste to Atom or VSCode or iTerm quotes ate not omitted. Is there a way to copy to clipboard without quotes instead of depending on behaviour of the application I'm pasting to? – Pramod Jangam Apr 28 '21 at 11:17
0

this is a common issue. the solution would be to paste your copy into fx bar instead of cell selection. this way you can skip the additional quotes

player0
  • 124,011
  • 12
  • 67
  • 124
-2

its very easy guys just follow as i say

step1: type letter in a cell

step2: select logo(A) with 4 dash(-) which is on top

step3: select cell

step4:turn on wrap text

problem solved

if you are using desktop

mac: alt/opttion+enter

windows: alt+enter

you can also select all cell at once then you can select format on menu bar and select text warping and then wrap(this is not recommended as it may destroy your table format)

pujan rai
  • 9
  • 2
-2

The easiest of the easiest solution is to copy straight from the cell. Mark the text within the cell instead of marking the whole cell and then copy.

Damian Budzinski
  • 17
  • 1
  • 7