How to copy multi-line text from Excel without quotes?

When you create a multi-line string in an excel cell (by using Alt-Enter), if you copy that cell to a text editor, excel will automatically add double quotes (") around the full string, ie:

Cell1  |   Simple String 1 
Cell2  |   First line of a 
       |   Multiline string
       |   with 3 lines 
Cell3  |   Another simple line 2

When you copy just the column with values to a text editor, we get:

Simple String 1
"First line of a 
Multiline string
with 3 lines"
Another simple line 2

How can I tell excel not to add the quote around multi-line text when copying from excel?


Edit: Text Editors that I've tried that display this behaviour:

  • MS Word
  • Wordpad
  • Notepad
  • Notepad++
  • SQL Server Studio

If you have a suggestion on using a particular editor (or one of the above) please tell me which one & how to use it...


Solution 1:

How your text gets copied in to Word depends on the formatting option chosen. In Word 2010, the default format option is HTML Format. There are four main options for how to copy text into Word. (Formatted Text (RTF), Unformatted Text, HTML Format, & Unformatted Unicode Text)

Pasting in with formatted text creates mini tables in Word. (The blue outlines.)

Paste Special Options

To get unformatted text in Word without the double quotes:

  1. Paste the text in formatted so it creates the table.
  2. Select the table and copy it.
  3. Move to a blank spot and paste the new copy as unformatted text. (Alt + E, S)

This also works to paste the results without quotes into another editor. Simply alter step 3 to paste into the other editor.

It would probably be faster however, to simply paste as normal and then use Replace to find and remove all double quotes.

Solution 2:

If you copy a range from Excel (2010) to Word, you will get the text as you want it, quotes free. Then you may copy it again to its final destination, eg notepad. Excel->Word->Notepad will give you the results that you want.

Solution 3:

I know this is year 2018, and these posts are older but still they are relevant. They've helped me figure out a solution.

  1. I put =A2 which is looking at the cell with the Char in the formula.
  2. I then copied A2 and pasted as values into another cell for example A3.
  3. I then selected A3, pressed F2, CtrlShiftHome, CtrlC.
  4. Stop right there don't paste into another excel cell. leave it in clipboard and paste into notepad.
  5. The invisible quotes disappear.

Disappear like INXS said, Disappear! :=)

Someone else may have said this solution already, so my apologies, after reading all of the examples something sunk into my thoughts and you've all helped!

Solution 4:

I've had this problem today so thought I'd post an answer in case anyone is still stuck on it.

You can get around this by highlighting the cell and then copy and pasting the code directly out of the text bar at the top by clicking in to it and highlighting it all manually.

I've only done this in Excel 2010 so I don't know if this will work for earlier versions.