Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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...

share|improve this question
3  
For what it's worth, it's because Excel tries to output valid CSV. I'd be surprised if this was easy to change. –  slhck Aug 17 '11 at 10:38
    
@slhck I hope you're wrong (nice answer on that though!), as it would be very useful to be able to tell excel that I'm not copying to csv... –  Andrew Bickerton Aug 17 '11 at 10:44
    
This is very annoying. There is no legitimate reason why Excel is putting quotes around text that contains a carriage return. –  oscilatingcretin May 31 '12 at 12:33
1  
Google Spreadsheets does the same annoying thing. I'm trying to copy several cells of data into a text file, but Google Spreadsheets adds quotes to the data - the quotes are NOT in the source data. This is a bug to me, not a feature. –  Jonny Oct 25 '12 at 10:33

8 Answers 8

up vote 14 down vote accepted

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.

share|improve this answer
    
+1 nice answer with technique to solve it! (unfortunately for the block I was dealing with, there were valid double quotes that I wanted to keep) –  Andrew Bickerton Jan 31 '12 at 9:18
    
+1 these stupid things really make me wanna look for a different office product. Maybe star office or something. thanks for the trick –  bicycle Jul 2 '13 at 5:43

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.

share|improve this answer
    
Word does display this behaviour, when you copy with other cells (as described in the question). Or is this something they've sorted in Office 2010? –  Andrew Bickerton Jan 14 '12 at 15:30
    
So far, this works. It's too bad I have to use Word 2010 as a middle man to remove the quotes, though. –  oscilatingcretin May 31 '12 at 12:37

I had the same issue and used the following steps (I'm using Office 2007, but I think it works for later versions, as well):

  1. Selected the range of cells containing multi-line text from Excel that I want to paste into another editor, and then choose Copy.
  2. In Word 2007, I pasted the copied range of cells as a table.
  3. Select the table.
  4. On the Layout tab, choose Select-Table.
  5. In the data section, choose Convert to Text and choose Paragraph marks.

The result is the original table converted to text, with newline characters at the end of each line and paragraph marks at the end of the last line from each cell.

share|improve this answer

You can save the Excel document as a web-page, and then copy the text from the webpage. This works well for a single column of data, but make sure you set the width of the column to the width of the text, otherwise it will add line-breaks.

share|improve this answer

It's not Excel's problem. Like the previous poster says, it's just outputting valid CSV data. It's going to be up to the editor your putting it into to look after formatting it. I would suggest using something a little smarter than notepad... You could use MS Word as a go-between from Excel to whatever text editor you wanted to use besides word.

share|improve this answer
    
I do use something other than notepad... Also Word does exactly the same thing (pasting just that cell in with "'s) –  Andrew Bickerton Aug 18 '11 at 7:00

I used the CLEAR function and it worked for me.

Put the cells you want to copy inside CLEAR, for example:

=clear(A1)

Where A1 is the cell with the data you want to copy to notepad without the quotes.

share|improve this answer
6  
I think you mean CLEAN. (At least in Excel 2010.) However that would remove the line feed characters and his second sentence would no longer be multiline. –  mischab1 Jan 31 '12 at 1:16

I know this is an old topic, but my solution was to use CLEAN as suggested, copy those cells and paste the values back into a sheet. That way, I could edit the text to the final result and copy and paste the cells into a text document without the quotes.

I think that cleaning the text first then doing the final formatting would most likely work (at least, it did for me).

share|improve this answer
    
One thing to note, I believe the "Clean" method will remove the carriage return characters as well and the resulting past will not be multi line. –  user154341 Aug 24 '12 at 14:26

Like you I had my data in a column. So to copy the values without those quotes, I concatenate the whole column with the data. This is for Google Docs spreadsheets but I guess it could work for Excel, might have to change the CONCATENATE function.

To concatenate the whole G column:

=CONCATENATE(G:G)

It added quotes only first and last and that's easy enough to edit by hand later.

share|improve this answer
    
doesn't work for excel –  Baljeetsingh Sucharia Mar 20 at 5:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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