Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
    
There is an answer that uses VBA posted here: stackoverflow.com/a/24913557/976210 This worked much better for me as I don't want to have to use an intermediary program. – oldmankit Jul 2 '15 at 2:46

12 Answers 12

up vote 19 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
1  
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
    
This works for Mac Office 2011 too. – Lewis42 Jul 30 '15 at 15:29

Easiest way that I've found is to concatenate the cells that you want to be on multiple lines with something "special" between them, rather than cr/lf. Tilde usually works well for me. For example, in column G:

=E1&"~"&F1
=E2&"~"&F2
...

In this example, the end goal is to get a text file with the values from E1 on one line, followed by F1 on a new line, followed by E2, F2, etc. Of course you could have just as easily built G1 from other values, just included ~ for the the line breaks.

Then to get multi-line text file in Notepad++

  • Cut and paste into Notepad++
  • Ctrl-H to open replace dialog
  • Make sure the "extended" search option is selected
  • Replace all ~ with \n (or \r\n if you prefer)
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
1  
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
    
Word 2010 with default settings works perfectly well as a go-between. – Adam Ryczkowski May 27 '15 at 9:49

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 can't be to prep it for a CSV file because they'd be pretty stupid be so sophisticated as to top and tail cells that contain non-printing characters with quotes and not do the same for cells that contain commas (which it doesn't).

Anyway. I came across this problem when I tried to create address labels from First Name, Surname, Address Line 1 ... fields as:

=A1&" "&B1&CHAR(13)&CHAR(10)&C1&CHAR(13)&CHAR(10)&D1

CHAR(13) followed by CHAR(10) being a new paragraph when viewing a text file with a hex editor.

My way round this annoyance is to:

  • copy and paste the cells/column into Word.
  • Use the clipboard symbol dropdown (bottom left of page or pasted text) to select, Keep text only.
  • Select the pasted text. You only have to do this if the document contains other stuff that might be affected
  • Hit Ctrl + H to bring up the Find and Replace dialogue box.
  • In the, Find what, field type in: "^p" (all four characters).
    You might have to go to Tools -> AutoCorrect options then both AutoFormat tabs to make sure it doesn't change your straight quotes)
  • In the, Replace with, field, enter: ^p (2 chars)
  • Now hit the, Replace All, button
  • Finally you will have to manually delete the double-quotes at the beginning and end of the imported block

This is based on Office 2003 so your version of Microsoft Word might be slightly different.

share|improve this answer

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

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.

share|improve this answer

Had the same problem, finally fixed it very easily. I had 600 rows of long product descriptions. To fix this issue, I did the following:

1) Select the entire column and click 'Wrap Text.'

2) Keeping the column selected, click 'Wrap Text' AGAIN (so that the text is 100% NOT wrapped).

PRESTO - the text copied out of each cell perfectly, without quotes (even though they were long paragraphs).

By default, Excel adds quotes to any multi-line text. So the simple solution, provided by the above process, is to ensure that your cells with long text are not automatically wrapping onto multiple lines.

It might be worthwhile to note that before I did this, the cells looked normal and did NOT appear to be wrapped. I think the process above 'tells' Excel to NOT interpret any cell as being 'multi-line' text, even if it is a long paragraph, thus solving the 'Excel adding quotes to copied text' issue.

share|improve this answer
1  
Hi, not quite the right solution. That only works if you don't have any internal carriage returns in your cells (press alt-enter to create an internal carriage return) – Andrew Bickerton May 1 at 20:31

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 Mar 20 '14 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.