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

Question

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

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.

Solution 5

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)

View more solutions

Comments

[Admin]

For what it's worth, it's because Excel tries to output valid CSV. I'd be surprised if this was easy to change.

[Admin]

This is very annoying. There is no legitimate reason why Excel is putting quotes around text that contains a carriage return.

[Admin]

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.

[Admin]

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.

[Admin]

It's not just carriage returns. The same thing happens with tab characters CHAR(9).

[Admin]

Here is a work around stackoverflow.com/a/60251126/1279373

[Andrew Bickerton]

I do use something other than notepad... Also Word does exactly the same thing (pasting just that cell in with "'s)

[Andrew Bickerton]

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]

+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)

[oscilatingcretin]

So far, this works. It's too bad I have to use Word 2010 as a middle man to remove the quotes, though.

[j_v_wow_d]

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.

[Baljeetsingh]

doesn't work for excel

[Adam Ryczkowski]

Word 2010 with default settings works perfectly well as a go-between.

[lewis]

This works for Mac Office 2011 too.

[Andrew Bickerton]

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)

[VSB]

As a developer i like this one since it does not requires 3rd tool like Word.

[nvuono]

This is perfect although I had to take my cell that had the CONCAT(A1:A100) formula in it and Paste Special into a second cell before I could select everything from the text box like you described

[alvaroc]

I discovered this yesterday. I agree with Andrew Bickerton. I don´t want to copy CSV, I want to copy text. Excel should at least ask. Since my text was about two thousand SQL inserts, I was able to solve it moreless easily with PsPad. Despite of being easy, it was completely unnecesary.

[w0mbat]

Works in current Mac Office 365.

[emjaySX]

This is the answer which should be getting the love! Simple, effective, requires no other steps than to copy from the formula bar instead of the cell itself. Thanks @Matt!

[timtak]

Using Excel>Word>Notepad I find that the newlines that I have added into Excel cells have disappeared when I pasted from Word back to Notepad, so I paste to Notepad, then back to Word, then replace the last character on each line (which is fortunately the same uniqute character in my text) with the same character ^13^10 then paste back to Notepad! If I don't use the Word route then not only are quotes added at the end of lines but all double quotes are doubled up (escaped) as well.

[BryanH]

Doesn't work for Excel 365 for Mac - the quotes are still there when I paste

[Andreas Warberg]

This should be the answer or at least deserves more upvotes. No extra tools required. Simple change (wrapping) of the formula.

[Admin]

Gosh is this the best way? :(

[Admin]

Just use CLEAN function. Reference stackoverflow.com/questions/24910288/…

[Admin]

You might be older than I am ha ha