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

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

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

Answer 3

1. I typed =A2, which references 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! :=)

Answer 4

I used the CLEAN function and it worked for me.

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

=clean(A1)

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

Answer 5

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.

Answer 6

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)

Answer 7

Install the generic/text only printer in Windows, selecting Print to file as a port.

Print your selected range to this printer - the text file it creates will be free of quotes.

Answer 8

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.

Answer 9

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 you're 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.

Answer 10

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.

Answer 11

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.

Answer 12

I had the same issue and I did that:

1. Click New Email in Outlook.
2. Paste ( Ctrl + V ) what you've been copied from Excel into it.
3. Select all ( Ctrl + A ) and recopy all ( Ctrl + C ).
4. Paste it anywhere you want.

Answer 13

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

Answer 14

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.

Answer 15

I found an easy work around for this. Install OpenOffice Calc and open the .xlsx file. Then simply copy the cell contents and paste into any text editor. The annoying leading and trailing quotes will not appear.

Answer 16

I was with the same problem and none of the solutions of this post helped me. Then I'll share the solution which definitely worked well for me, in case others may be in the same situation.

First, this solution also complies with one bug recently reported to Microsoft, which was causing the clipboard content to be transformed into unreadable content, after any modification using VBA when the user accessed any "Quick Acces Folder" using file explorer.

Documentation for the solution of the copy past bug, which the code will be used in this answer, to remove the quotes from clipboard: https://docs.microsoft.com/en-us/office/vba/access/Concepts/Windows-API/send-information-to-the-clipboard

You'll need to build a macro as below, and assign the "ctrl+c" as a hotkey to it. (Hotkey assignment = Developer tab, Macros, click the macro, options, then put the letter "c" in the hotkey field).

Sub ClipboardRemoveQuotes()
    Dim strClip As String
    strClip = Selection.Copy
    strClip = GetClipboard()
    On Error Resume Next - Needed in case clipboard is empty
    strClip = Replace(strClip, Chr(34), "") 
    On Error GoTo 0
    SetClipboard (strClip)
End Sub

This will still need for you to build the functions "SetClipboard" and "GetClipboard".

Below we have the definition of the "SetClipboard" and "GetClipboard" functions, with a few adjustments to fit different excel versions. (Put the below code in a module)

    Option Explicit
#If VBA7 Then
    Private Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hWnd As LongPtr) As LongPtr
    Private Declare PtrSafe Function EmptyClipboard Lib "User32" () As LongPtr
    Private Declare PtrSafe Function CloseClipboard Lib "User32" () As LongPtr
    Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "User32" (ByVal wFormat As LongPtr) As LongPtr
    Private Declare PtrSafe Function GetClipboardData Lib "User32" (ByVal wFormat As LongPtr) As LongPtr
    Private Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat As LongPtr, ByVal hMem As LongPtr) As LongPtr
    Private Declare PtrSafe Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As Long, ByVal dwBytes As Long) As LongPtr
    Private Declare PtrSafe Function GlobalLock Lib "kernel32.dll" (ByVal hMem As LongPtr) As LongPtr
    Private Declare PtrSafe Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As LongPtr) As LongPtr
    Private Declare PtrSafe Function GlobalSize Lib "kernel32" (ByVal hMem As LongPtr) As Long
    Private Declare PtrSafe Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
#Else
    Private Declare Function OpenClipboard Lib "user32.dll" (ByVal hWnd As Long) As Long
    Private Declare Function EmptyClipboard Lib "user32.dll" () As Long
    Private Declare Function CloseClipboard Lib "user32.dll" () As Long
    Private Declare Function IsClipboardFormatAvailable Lib "user32.dll" (ByVal wFormat As Long) As Long
    Private Declare Function GetClipboardData Lib "user32.dll" (ByVal wFormat As Long) As Long
    Private Declare Function SetClipboardData Lib "user32.dll" (ByVal wFormat As Long, ByVal hMem As Long) As Long
    Private Declare Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
    Private Declare Function GlobalLock Lib "kernel32.dll" (ByVal hMem As Long) As Long
    Private Declare Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As Long) As Long
    Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
    Private Declare Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Long, ByVal lpString2 As Long) As Long
#End If

Public Sub SetClipboard(sUniText As String)
    #If VBA7 Then
        Dim iStrPtr As LongPtr
        Dim iLock As LongPtr
    #Else
        Dim iStrPtr As Long
        Dim iLock As Long
    #End If
    Dim iLen As Long
    Const GMEM_MOVEABLE As Long = &H2
    Const GMEM_ZEROINIT As Long = &H40
    Const CF_UNICODETEXT As Long = &HD
    OpenClipboard 0&
    EmptyClipboard
    iLen = LenB(sUniText) + 2&
    iStrPtr = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, iLen)
    iLock = GlobalLock(iStrPtr)
    lstrcpy iLock, StrPtr(sUniText)
    GlobalUnlock iStrPtr
    SetClipboardData CF_UNICODETEXT, iStrPtr
    CloseClipboard
End Sub

Public Function GetClipboard() As String
#If VBA7 Then
    Dim iStrPtr As LongPtr
    Dim iLock As LongPtr
#Else
    Dim iStrPtr As Long
    Dim iLock As Long
#End If
    Dim iLen As Long
    Dim sUniText As String
    Const CF_UNICODETEXT As Long = 13&
    OpenClipboard 0&
    If IsClipboardFormatAvailable(CF_UNICODETEXT) Then
        iStrPtr = GetClipboardData(CF_UNICODETEXT)
        If iStrPtr Then
            iLock = GlobalLock(iStrPtr)
            iLen = GlobalSize(iStrPtr)
            sUniText = String$(iLen \ 2& - 1&, vbNullChar)
            lstrcpy StrPtr(sUniText), iLock
            GlobalUnlock iStrPtr
        End If
        GetClipboard = sUniText
    End If
    CloseClipboard
End Function

I hope it may help others as well as it helped me.

Answer 17

The other day, I stumbled upon the same old issue almost a decade after this topic was started. The proposed solutions here required additional steps, adding complexity that I wanted to avoid. So I found an alternative way that worked for me.

I needed to process some data in Excel and output a few numeric values integrated into XML code.

My Core Idea

Processed data (simplifed)

Item 1
Item 2

Desired output (simplified)

<li>Item 1</li>
<li>Item 2</li>

Initially, I tried to generate the output as multiline text in a single cell using CHAR(10). The text copied to my XML file looked like this

"<li>Item 1</li>
<li>Item 2</li>"

Then I came to understand that I simply need to create each line of code in a separate cell and copy the lines.

each line of code in a separate cell

That solved the issue for me.

And when you think of it, why create a multiline text in a single cell if you intend to insert it in some other software? If, for any reason, you also need to have it in a single cell in Excel, you can just create the text in multiple cells in another worksheet and concatenate it in your worksheet using CHAR(10) or CHAR(13).

My Actual Issue

I used Excel to calculate coordinates for some XML structures, then I needed to integrate the coordinates into the pre-defined lines of XML code.

svg:x="[coor_x]cm"
svg:y="[coor_y]cm"

Since I had many pairs of coordinates, it seemed natural to generate multiline text for each pair of coordinates so that I can drag the formula down easily.

each cell contains two lines for coor_x and coor_y

But the output inserted in a different software was less than desirable:

"svg:x=""1.43cm""
svg:y=""2.47cm"""
"svg:x=""0.53cm""
svg:y=""2.12cm"""

Another approach was to have a formula for coor_x in even rows and a different formula for coor_y in odd rows. But then I would lose the ability to drag formulas down as many rows as I need. I would need to copy and paste this pair of formulas below a lot of times and re-adjust wrong references to cells storing coordinates.

pair of formulas in two rows that need to be copied multiple times

Finally, once I found that my version of Excel supported dynamic arrays, it dawned on me how to do it properly. (I believe it should also work on older versions of Excel using named ranges.)

I generated all lines for coor_x in one column, then generated all lines for coor_y in another column, then used a modified version of the formula to stack data from the two columns into a single column:

=LET(a,TRANSPOSE(A8:B9),r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),arr)

generate all lines for coor_x in one column, generate all lines for coor_y in another column, use the formula below to stack data from multiple columns into a single column

Using dynamic arrays in creative ways, you can even do away with the need to adjust array regions manually when you add more pairs of coordinates.

Hope this solution will also be helpful!

Answer 18

Here's a different approach that can work with any software...

My use case includes quoted strings build in Excel (things like msgid "ThisLabel") that get doubled with the copy (msgid ""ThisLabel"").

I use Free Clipboard Viewer, which allows you to view all the different format of the copied data. With my text copied from Excel, I switch to Rich Text Format (RTF) and copy THAT. Everything is cleaned up and I can simply paste it anywhere I need it.

>

Answer 19

I have same problem with copying result from C1 procesed by formula. My intent was to prepare JSON.

Example of concatenate strings:

  A         B          C
1 string1   tabOnEnd   string1tabOnEnd

Old result of copy: "string1tabOnEnd "

The problem was in new line symbol and tab symbol. So I replace all new lines by \n and all tabs with \t

New result of copy: string1tabOnEnd\t

Replace formula: = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2; "\"; "\\"); """"; "\"""); CHAR(10); "\n"); " "; "\t")

Maybe you can use similar formula to remove tab symbol and new line symbol. It should prevent adding quotes. = SUBSTITUTE(SUBSTITUTE(B2; CHAR(10); ""); " "; "")

Answer 20

I encountered the same issue. Initially, I copied a range of cells and pasted them into a Word document. Afterward, I copied and pasted from there into my Notepad++.

Answer 21

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.

Answer 22

working on the answer from solvus, we can create an autopublish webpage, with just the range of text you want - without quotes. you can then copy and paste this to other locations. Once you set it up, its there forever and uptodate without messing with your main file.

to do this,...

Select your cells and then save as.. save as file type = Single File Web Page. with the option Republish $ : $(your selected range.)

click publish. select AutoRepublish every time this workbook is saved.

• you will then get a Webpage published which has just your ranged data, in a table format with no quotes. copy and paste :)