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

score 40 · Answer 1 · answered Jan 06 '12 at 03:38

40

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.

answered Jan 06 '12 at 03:38

s_a

2,159

score 28 · Accepted Answer · answered Jan 31 '12 at 01:35

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:

Paste the text in formatted so it creates the table.
Select the table and copy it.
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.

score 9 · Answer 3 · edited May 25 '23 at 11:19

9

I typed =A2, which references the cell with the CHAR in the formula.
I then copied A2 and pasted as values into another cell, for example A3.
I then selected A3, pressed F2, CtrlShiftHome, CtrlC.
Stop right there don't paste into another Excel cell. Leave it in clipboard and paste into Notepad.
The invisible quotes disappear.

Disappear like INXS said, Disappear! :=)

edited May 25 '23 at 11:19

Destroy666

12,350

answered Aug 11 '18 at 18:57

me.guest

91

score 7 · Answer 4 · edited Dec 02 '21 at 11:39

7

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.

edited Dec 02 '21 at 11:39

Andreas Warberg

103

answered Jan 05 '12 at 09:43

wizwig

87

score 6 · Answer 5 · edited May 25 '23 at 12:34

6

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.

edited May 25 '23 at 12:34

ahorn

137

answered Jun 17 '15 at 09:28

Matt

61

score 5 · Answer 6 · answered Feb 18 '16 at 14:14

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)

score 3 · Answer 7 · edited May 25 '23 at 11:18

3

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.

edited May 25 '23 at 11:18

Destroy666

12,350

answered Feb 05 '20 at 17:16

Guy Cooper

31
1

Michael Beck · Answer 8 · 2022-09-23T17:04:59.637

3

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.

edited Sep 23 '22 at 17:04

answered Aug 17 '11 at 19:41

Michael Beck

164

score 2 · Answer 9 · answered Dec 26 '13 at 00:24

2

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.

answered Dec 26 '13 at 00:24

solvus

21

f-hollow · Answer 10 · 2024-06-07T03:20:34.030

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),
  c,COLUMNS(a),
  seq,SEQUENCE(r*c,,0),
INDEX(a,MOD(seq,r)+1,seq/r+1)
)

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!

score 1 · Answer 11 · edited Aug 03 '21 at 17:46

1

I had the same issue and I did that:

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

edited Aug 03 '21 at 17:46

Josh Zhang

946

answered Aug 03 '21 at 10:02

Erdem

11

score 1 · Answer 12 · edited Apr 02 '13 at 11:42

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

Selected the range of cells containing multi-line text from Excel that I want to paste into another editor, and then choose Copy.
In Word 2007, I pasted the copied range of cells as a table.
Select the table.
On the Layout tab, choose Select-Table.
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.

score 0 · Answer 13 · answered May 01 '16 at 20: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.

score 0 · Answer 14 · edited May 14 '16 at 16:10

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.

score 0 · Answer 15 · answered Jan 18 '18 at 12:13

0

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.

answered Jan 18 '18 at 12:13

user863611

1

score 0 · Answer 16 · answered Feb 13 '21 at 18:21

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.

score 0 · Answer 17 · answered Mar 19 '21 at 11:14

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.

RTF version > Copy from Excel

Vladimír Mlázovský · Answer 18 · 2022-10-31T14:06:59.483

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); ""); " "; "")

score 0 · Answer 19 · answered Aug 15 '23 at 06:00

0

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

answered Aug 15 '23 at 06:00

Mathew Kariuki

1

score 0 · Answer 20 · answered Jun 05 '24 at 12:13

If you can change the spreadsheet, you can split it up into into individual lines... (In my case, the separator used between lines was CHAR(10)) (The individual line realisation is thanks to this answer)

In my case, I have a single multiline string in cell B1.

For that case, I create the following formula in cell C2:

=IFERROR(FIND(CHAR(10),CONCAT($B$1,CHAR(10)),IFERROR(C1,0)+1),999999999999)

and this formula in Cell D2:

=IFERROR(MID($B$1,IFERROR(C1,0)+1,C2-IFERROR(C1,0)-1),"")

This is then copied up to C1 and D1. (I did it by dragging the bottom right corner of the cell) (A method that shifts the non-absolute references are needed for the copy). It also needs to be dragged down (or otherwise copied) enough rows to get all the strings.

The first formula finds the position of the next newline (CHAR(10)) =, with the IFERROR handling the reference errors if it was dragged to the edge of the file (e.g. if the multiline string is on row 1) (that is also why the formula was entered one line down and expanded upward, since the first cell will get a reference error). A CHAR(10) is concatenated to the end of the string in order for the last line's end to be FINDable.

The second formula grabs the relevant line from the multi-line string into its cell.

In both formulas, the outer IFERRORs is to stop the output at the end of the string Without it, the other IFERRORs results in the result being repeated. The position finder one uses a large value to return a position outside the string, the string extractor one uses one that outputs a blank string.

The content of column D should the work pasted without characters escaped on quotes around the content. (In my case it was a HCL file for use in terraform being generated)

For the specific example in the question, assuming the data is in cells A1:A3, use this formula in cell B1 to get a single multi-line string:

=TEXTJOIN(CHAR(10),TRUE,A1:A3)

This turns all those cells into one long multi-line string in B1. The rest of the formulas stays the same and in the same positions.

Some notes:

In both cases it might make sense to create a separate sheet in the workbook for the copying, if changing the sheet with the strings can't be modified that much.
I use Ctrl-down arrow to select all the lines (in column D in the examples above). It will select as far as the formula is extended, not as far as lines are populated, so the paste would likely contain some blank lines at the end. (unless the formula is extended to only the exact row needed for the last line of the string)

score 0 · Answer 21 · edited Oct 01 '12 at 11:39

0

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

edited Oct 01 '12 at 11:39

Dave

25,513

answered Aug 17 '12 at 15:20

Alan Reeves

1

score -1 · Answer 22 · answered Dec 01 '16 at 12:42

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

score -1 · Answer 23 · answered Oct 25 '12 at 10:40

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.

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

23 Answers23

My Core Idea

My Actual Issue

Linked