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

copy/pasteformattingmicrosoft excel

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…

Best Answer

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.

Related Question