从单元格复制时省略引号

If you try pasting into Word-Pad, Notepad++ or Word you wouldn't have this issue. To copy the cell value as pure text, to achieve what you describe you have to use a macro:

In the workbook where you want this to apply (or in your Personal.xls if you want to use across several workbooks), place the following code in a standard module:

Code:

Sub CopyCellContents()
'create a reference in the VBE to Microsft Forms 2.0 Lib
' do this by (in VBA editor) clicking tools - > references and then ticking "Microsoft Forms 2.0 Library"
Dim objData As New DataObject
Dim strTemp As String
strTemp = ActiveCell.Value
objData.SetText (strTemp)
objData.PutInClipboard
End Sub

To add a standard module to your project (workbook), open up the VBE with Alt+F11 and then right-click on your workbook in the top left Project Window and select Insert>Module. Paste the code into the code module window which will open on the right.

Back in Excel, go Tools>Macro>Macros and select the macro called "CopyCellContents" and then choose Options from the dialog. Here you can assign the macro to a shortcut key (eg like CTRL+C for normal copy) - I used CTRL+Q.

Then, when you want to copy a single cell over to Notepad/wherever, just do Ctrl+q (or whatever you chose) and then do a CTRL+V or Edit>Paste in your chosen destination.

My answer is copied (with a few additions) from: here

EDIT: (from comments)

If you don't find Microsoft Forms 2.0 Library in the references list, You can try

• looking for FM20.DLL instead (thanks @Peter Smallwood)
• clicking Browse and selecting C:\Windows\System32\FM20.dll (32 bit Windows) (thanks @JWhy)
• clicking Browse and selecting C:\Windows\SysWOW64\FM20.dll (on 64-bit)

I just had this problem and wrapping each cell with the CLEAN function fixed it for me. That should be relatively easy to do by doing =CLEAN(, selecting your cell, and then autofilling the rest of the column. After I did this, pastes into Notepad or any other program no longer had duplicate quotes.

First paste it into Word, then you can paste it into notepad and it will appear without the quotes

To keep line breaks when pasting in notepad, replace this line in the macro:

strTemp = ActiveCell.Value

by:

strTemp = Replace(ActiveCell.Value, Chr(10), vbCrLf)

Possible problem in relation to answer from "user3616725":
Im on Windows 8.1 and there seems to be a problem with the linked VBA code from accepted answer from "user3616725":

Sub CopyCellContents()
' !!! IMPORTANT !!!:
' CREATE A REFERENCE IN THE VBE TO "Microsft Forms 2.0 Library" OR "Microsft Forms 2.0 Object Library"
' DO THIS BY (IN VBA EDITOR) CLICKING TOOLS -> REFERENCES & THEN TICKING "Microsoft Forms 2.0 Library" OR "Microsft Forms 2.0 Object Library"
Dim objData As New DataObject
Dim strTemp As String
strTemp = ActiveCell.Value
objData.SetText (strTemp)
objData.PutInClipboard
End Sub

Details:
Running above code and pasting clipboard into a cell in Excel I get two symbols composed of squares with a question mark inside, like this: ⍰⍰. Pasting into Notepad doesn't even show anything.

Solution:
After searching for quite some time I found another VBA script from user "Nepumuk" which makes use of the Windows API. Here's his code that finally worked for me:

Option Explicit


Private Declare Function OpenClipboard Lib "user32.dll" ( _
ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "user32.dll" () As Long
Private Declare Function EmptyClipboard Lib "user32.dll" () 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 GlobalFree Lib "kernel32.dll" ( _
ByVal hMem As Long) As Long
Private Declare Function lstrcpy Lib "kernel32.dll" ( _
ByVal lpStr1 As Any, _
ByVal lpStr2 As Any) As Long


Private Const CF_TEXT As Long = 1&


Private Const GMEM_MOVEABLE As Long = 2


Public Sub Beispiel()
Call StringToClipboard("Hallo ...")
End Sub


Private Sub StringToClipboard(strText As String)
Dim lngIdentifier As Long, lngPointer As Long
lngIdentifier = GlobalAlloc(GMEM_MOVEABLE, Len(strText) + 1)
lngPointer = GlobalLock(lngIdentifier)
Call lstrcpy(ByVal lngPointer, strText)
Call GlobalUnlock(lngIdentifier)
Call OpenClipboard(0&)
Call EmptyClipboard
Call SetClipboardData(CF_TEXT, lngIdentifier)
Call CloseClipboard
Call GlobalFree(lngIdentifier)
End Sub

To use it the same way like the first VBA code from above, change the Sub "Beispiel()" from:

Public Sub Beispiel()
Call StringToClipboard("Hallo ...")
End Sub

To:

Sub CopyCellContents()
Call StringToClipboard(ActiveCell.Value)
End Sub

And run it via Excel macro menu like suggested from "user3616725" from accepted answer:

Back in Excel, go Tools>Macro>Macros and select the macro called "CopyCellContents" and then choose Options from the dialog. Here you can assign the macro to a shortcut key (eg like Ctrl+c for normal copy) - I used Ctrl+q.

Then, when you want to copy a single cell over to Notepad/wherever, just do Ctrl+q (or whatever you chose) and then do a Ctrl+v or Edit>Paste in your chosen destination.

Edit (21st of November in 2015):
@ comment from "dotctor":
No, this seriously is no new question! In my opinion it is a good addition for the accepted answer as my answer addresses problems that you can face when using the code from the accepted answer. If I would have more reputation, I would have created a comment.
@ comment from "Teepeemm":
Yes, you are right, answers beginning with title "Problem:" are misleading. Changed to: "Possible problem in relation to answer from "user3616725":". As a comment I certainly would have written much more compact.

Please use the below formula

=Clean("1"&CHAR(9)&"SOME NOTES FOR LINE 1."&CHAR(9)&"2"&CHAR(9)&"SOME NOTES FOR LINE 2.")

and you will get what you want ;-)

If you want to select multiple cells and copy their values to the clipboard without all those annoying quotes the following code may be useful. This is an enhancement of the code given above from user3616725.

Sub CopyCells()
'Attach Microsoft Forms 2.0 Library: tools\references\Browse\FM20.DLL
'Then set a keyboard shortcut to the CopyCells Macro (eg Crtl T)
Dim objData As New DataObject
Dim cell As Object
Dim concat As String
Dim cellValue As String
CR = ""
For Each cell In Selection
If IsNumeric(cell.Value) Then
cellValue = LTrim(Str(cell.Value))
Else
cellValue = cell.Value
End If
concat = concat + CR + cellValue
CR = Chr(13)
Next
objData.SetText (concat)
objData.PutInClipboard
End Sub

My solution when I hit the quotes issue was to strip carriage returns from the end of my cells' text. Because of these carriage returns (inserted by an external program), Excel was adding quotes to the entire string.

It's also possible to remove these double-quotes by placing your result on the "Clean" function.

Example:

=CLEAN("1"&CHAR(9)&"SOME NOTES FOR LINE 1."&CHAR(9)&"2"&CHAR(9)&"SOME NOTES FOR LINE 2.")

The output will be pasted without the double-quotes on other programs such as Notepad++.

"If you want to Select multiple Cells and Copy their values to the Clipboard without all those annoying quotes" (without the bugs in Peter Smallwood's multi-Cells solution) "the following code may be useful." This is an enhancement of the code given above from Peter Smallwood (which "is an enhancement of the code given above from user3616725"). This fixes the following bugs in Peter Smallwood's solution:

• Avoids "Variable not defined" Compiler Error (for "CR" - "clibboardFieldDelimiter " here)
• Convert an Empty Cell to an empty String vs. "0".
• Append Tab (ASCII 9) vs. CR (ASCII 13) after each Cell.
• Append a CR (ASCII 13) + LF (ASCII 10) (vs. CR (ASCII 13)) after each Row.

NOTE: You still won't be able to copy characters embedded within a Cell that would cause an exit of the target field you're Pasting that Cell into (i.e. Tab or CR when Pasting into the Edit Table Window of Access or SSMS).

Option Explicit


Sub CopyCellsWithoutAddingQuotes()


' -- Attach Microsoft Forms 2.0 Library: tools\references\Browse\FM20.DLL
' -- NOTE: You may have to temporarily insert a UserForm into your VBAProject for it to show up.
' -- Then set a Keyboard Shortcut to the "CopyCellsWithoutAddingQuotes" Macro (i.e. Crtl+E)


Dim clibboardFieldDelimiter As String
Dim clibboardLineDelimiter As String
Dim row As Range
Dim cell As Range
Dim cellValueText As String
Dim clipboardText As String
Dim isFirstRow As Boolean
Dim isFirstCellOfRow As Boolean
Dim dataObj As New dataObject


clibboardFieldDelimiter = Chr(9)
clibboardLineDelimiter = Chr(13) + Chr(10)
isFirstRow = True
isFirstCellOfRow = True


For Each row In Selection.Rows


If Not isFirstRow Then
clipboardText = clipboardText + clibboardLineDelimiter
End If


For Each cell In row.Cells


If IsEmpty(cell.Value) Then


cellValueText = ""


ElseIf IsNumeric(cell.Value) Then


cellValueText = LTrim(Str(cell.Value))


Else


cellValueText = cell.Value


End If ' -- Else Non-empty Non-numeric


If isFirstCellOfRow Then


clipboardText = clipboardText + cellValueText
isFirstCellOfRow = False


Else ' -- Not (isFirstCellOfRow)


clipboardText = clipboardText + clibboardFieldDelimiter + cellValueText


End If ' -- Else Not (isFirstCellOfRow)


Next cell


isFirstRow = False
isFirstCellOfRow = True


Next row


clipboardText = clipboardText + clibboardLineDelimiter


dataObj.SetText (clipboardText)
dataObj.PutInClipboard


End Sub

You can do this in an Excel macro via VBA, sending the results to a file:

Sub SimpleVBAWriteToFileWithoutQuotes()
Open "c:\TEMP\Excel\out.txt" For Output As #1
Print #1, Application.ActiveSheet.Cells(2, 3)
Close #1
End Sub

And if you are wanting to get filenames and content into multiple files, here is a short snippet that avoids the double quotes around the output.

Sub DumpCellDataToTextFilesWithoutDoubleQuotes()
' this will work for filename and content in two different columns such as:
' filename column       data column
' 101                   this is some data
' 102                   this is more data


Dim rngData As Range
Dim strData As String
Dim strTempFile As String
Dim strFilename As String
Dim i As Long
Dim intFilenameColumn As Integer
Dim intDataColumn As Integer
Dim intStartingRow As Integer


intFilenameColumn = 1     ' the column number containing the filenames
intDataColumn = 3         ' the column number containing the data
intStartingRow = 2        ' the row number to start gathering data




For i = intStartingRow To Range("A1", Range("A1").End(xlDown)).Rows.Count


' copy the data cell's value
Set rngData = Application.ActiveSheet.Cells(i, intDataColumn)


' get the base filename
strFilename = Application.ActiveSheet.Cells(i, intFilenameColumn)


' assemble full filename and path
strTempFile = "w:\TEMP\Excel\" & strFilename & ".txt"


' write to temp file
Open strTempFile For Output As #1
Print #1, rngData
Close #1


Next i


' goto home cell
Application.ActiveSheet.Cells(1, 1).Select
Range("A1").ClearOutline
End Sub

• if formula having multi line (means having line break in formula) then copy paste will work in that way
• if can remove multi line then no quotes will appear while copy paste.
• else use CLEAN function as said by @greg in previous answer

Note:The cause of the quotes is that when data moves from excel to clipboard it is fully complying with CSV standards which include quoting values that include tabs, new lines etc (and double-quote characters are replaced with two double-quote characters )

So another approach, especially as in OP's case when tabs/new lines are due to the formula, is to use alternate characters for tabs and hard returns. I use ascii Unit Separator =char(31) for tabs and ascii Record Separator =char(30) for new lines.

Then pasting into text editor will not involve the extra CSV rules and you can do a quick search and replace to convert them back again.

If the tabs/new lines are embedded in the data, you can do a search and replace in excel to convert them.

Whether using formula or changing the data, the key to choosing delimiters is never use characters that can be in the actual data. This is why I recommend the low level ascii characters.

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://learn.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.

Much easier method:
Paste first into a draft new email - no quotes will be included.
Ctrl-A and copy again from there, discard the draft email.

Same problem: I need the outcame of Excel formulas (including newlines, but with no extra quotes) as input for a console program.

My personal solution: instead of program, I run squ | program (or squ | program > OUT) and then copy/paste the content of the cells into the console (a manual [Ctrl]+[Z] may be needed at the end).

The source of squ is:

#include <stdio.h>


int main(void)
{
char c[2];
while(fgets(c,2,stdin)) if(*c!=34) fputs(c,stdout);
return 0;
}