How to copy multi-line text (hard Return) from Excel without extra quotes?

Jorge Jorhoe

I see a lot questions on this . . . Can Notepad++ do whatever Word does, to remove quotes, or is there a plugin? Then I can just copy from Excel, and paste into Notepad++

These are the best answers I’ve seen

#1. How to copy multi-line text from Excel without quotes? . . . http://superuser.com/questions/324271/how-to-copy-multi-line-text-from-excel-without-quotes

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

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.

#2. create your own macro that creates the output text file . . .

Getting Rid of Extra Quote Marks in Exported Text Files . . . http://excel.tips.net/T002237_Getting_Rid_of_Extra_Quote_Marks_in_Exported_Text_Files.html

#3. Excel - leave out quotes when copying from cell . . . http://stackoverflow.com/questions/24910288/excel-leave-out-quotes-when-copying-from-cell/24913557#

. . . Have you tried changing the format to something other than number? I think I have seen ways around that . . . Number, General, Text (doesn’t even Concatenate then) - no luck

. . . To copy the cell value as pure text, to achieve what you describe you have to use a macro:

#4. This gets the quotes: . . . Use the Clean() function in Excel . . . However, it also removes the line feed characters

Jorge Jorhoe

Something I’ve found out:

In Word 2010, if you Right-click / “Paste Special,” the Paste Options give you the following:

Keep Source Formatting (K) . . . no quotes, good
Merge Formatting (M) . . . no quotes, good
Link & Keep Source Formatting (F) . . . no quotes, good
Link & Merge Formatting (L) . . . no quotes, good
Picture (U) . . . a few text characters in a box - I’m not pasting a picture
Keep Text Only (T) . . . quotes, like what I see in NP++ and Notepad

Any program which can’t handle formatting marks, or non-printing characters, will give you the quotes.

The default paste in Word “understands” the line breaks from Excel, and retains them - Notepad++ retains them, also

Notepad doesn’t understand the line breaks, though, so either ignores them entirely, or will indicate to the user that there is a “not-understood” character in the pasted string, by putting quote marks around it.

What’s happening in Notepad is by design, and there’s probably nothing you can do to fix it - but what about in Notepad++ ?

Jorge Jorhoe

I see that MS WordPad does not add the quotes, either

Jorge Jorhoe

http://excel.tips.net/T002237_Getting_Rid_of_Extra_Quote_Marks_in_Exported_Text_Files.html

Sometimes the export filters used by Excel can produce undesired results in the exported file.

For instance, when you export to a tab-delimited text file, some Excel filters place quote marks around the text in a cell.

[ Is Notepad++ tab delimited . . . Could this be similar to what happens when you copy and paste? ]

Maybe the TextFX plugin has something . . . http://sourceforge.net/projects/npp-plugins/files/TextFX/

Jorge Jorhoe

Edit / Paste Special / Paste HTML Content . . . too many extra characters

Edit / Paste Special / Paste RTF Content . . . too many extra characters

Edit / Paste Special / Paste Binary Content . . . no extra characters, but it has the quotes / double quotes

Jorge Jorhoe

NP++ is not doing something to
CR = carriage return
and
LF = line fed
that
MS Word and MS WordPad are doing

Is it that NP++ does not understand what is CR and LF ? Seems like it.

Jorge Jorhoe

But, if I type the following (below) into MS Word, and then copy and paste it into NP++, I get the exact same thing, with no double quotes . . . and there is a CR LF after each letter . . . so, it seems like CR LF is not the issue, it’s something else . . . Does something else copy from the Excel cell that is hidden? I took a look at what was in there with an Excel Add-In called “Cell View,” but all I saw were the letters.

A

“B”

C

D

Pete Wright

Hello!

I will revive this ancient post, because I have exactly the same problem.

Until now I still haven’t found a solution.
Is there a workaround or a plugin that does the job?

I need the copied contend to have Line Breaks, but no double quotes.
I have a code generated in Excel by string concatenation which I then use as a scripting file for AutoCAD and it must not contain the quotes.

Any ideas?
Why can I paste the text into word without quotes, but not into Notepad++?

Many thanks in advance

PeterJones

@Pete-Wright,

Notepad++ actually handles this better than some other text editors, like Windows notepad.exe (which will put it all on one line, because it doesn’t recognize the standalone LF in the clipboard as a newline sequence)

The problem is not with Notepad++. The “problem” is with the way that Excel correctly handles its data as it goes into the clipboard – in that Microsoft is actually following an external, industry-normal standard – the horror!

When you copy a range of cells from Excel, behind the scenes, Excel puts multiple items in the clipboard: often, one is the binary information that Excel likes, so it can easily keep things like formats, formulas, etc; another is a plain-text version of the cells’ data. When Notepad++ (or any other text editor) pastes from the clipboard, it asks for the plain-text entry in the clipboard. So far, so good.

Now, the “problem” comes because Excel wants spreadsheet-like copy-buffers, even in plain-text format, to be interpreted as rows and columns of data. To do that, it uses the industry-standard Tab Separated Values format (TSV: a variant of Comma Separated Values CSV, but it uses the tab character, rather than commas, for separating columns in the same row). The definitions for TSV/CSV require that if there are special characters – either the column-separator character (TAB or COMMA, depending) or the row-separator (newline, usually CRLF or LF), then it must be enclosed in quotes, so that the receiving application can properly interpret columns and rows.

Why can I paste the text into word without quotes, but not into Notepad++?

MS Word, being another Microsoft product, probably recognizes the binary format from the binary portion of the clipboard, and converts that directly into a MS Word table.

Any ideas?

I don’t know of any existing plugins that will ~~mangle this perfectly-valid TSV data~~ convert TSV data from the clipboard into the particular format you want. There are, however, many potential workarounds:

If you have a well-defined format, which will not contain any other quote marks other than the ones that Excel included in the TSV on the clipboard, you could just select everything that you just pasted from Excel, and remove quote marks. This is probably the easiest, though the least automated.
Wait for someone else to answer, who does know of a plugin that will transform the clipboard’s TSV into the non-quoted format that you want
Write a plugin that allows you to transform TSV in the particular way you want to (or find someone who will write it for you, either for free or for pay)
Use the existing PythonScript or LuaScript plugins to write a script in the Python or Lua programming languages, which will translate the TSV from the clipboard into the format you want (or find/pay someone else)
In Excel, you could use multiple rows of formulas, rather than embedding newlines in a single cells’ formula, when creating the AutoCAD commands, so that there are no embedded tabs or newlines, so that Excel doesn’t try to wrap quotes around the cell value when putting them into the clipboard
You could use Excel’s VBA (Visual Basic for Applications) to take the contents of the cells you care about, and write them directly to a text file, rather than going through the clipboard, so that you can decide on exactly the right format. You can then open the resulting AutoCAD directly in AutoCAD, or you can do further manipulation inside. (I would recommend the previous “multiple rows of formulas” in addition to this, really, as embedding newlines in Excel cells is error prone at best)
You could use a different tool (a standalone implementation of a programming language like Perl, Python, Lua, Visual Basic, etc) for generating the AutoCAD script – either by creating the AutoCAD script itself with its own formulas, or by using OLE or a library that knows how to read .xls and/or .xlsx directly and grab the cell values and places them into your output file.

The order that I would recommend trying things: #2 (but don’t wait too long), #1, #5, #6, #4, #7, #3.

(edit: move #4 before #7 in my order of recommendations; getting OLE working right and/or parsing .xls/.xlsx manually can be tricky, even with a good module/library that knows how to do those things.)

Ekopalypse

@Pete-Wright

maybe this is/sounds stupid but why not just replacing the double quotes with nothing afterwards?
I mean, if this is a repetitive task you might even think about creating a macro to make your life easier.
But I guess I’ve missed something here.