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

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

6

20 Answers

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.

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.

5

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!

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.

3

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)

1

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.

3

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.

I found this problem too.

My fix:

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.

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.

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.

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.

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.

2

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

1

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.

1

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.

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:

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.

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!

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.

>

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.

1

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Answer This Question