Problem:
When copying a cell from Excel outside of the program, double-quotes are added automatically.
Details:
I'm using Excel 2007 on a Windows 7 machine. If I have a cell with the following formula:
="1"&CHAR(9)&"SOME NOTES FOR LINE 1."&CHAR(9)&"2"&CHAR(9)&"SOME NOTES FOR LINE 2."
The output in the cell (formatted as number) looks like this in Excel:
1SOME NOTES FOR LINE 1.2SOME NOTES FOR LINE 2.
Well and good. But, if I copy the cell into another program, such as notepad, I get annoying double-quotes at the beginning and end. Notice the tabs created by "CHAR(9)" are kept, which is good.
"1 SOME NOTES FOR LINE 1. 2 SOME NOTES FOR LINE 2."
How can I keep these double-quotes from showing up, when I copy to another program? In other words, can I keep these from being automatically added when the cell is copied to clipboard?
"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:
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
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.
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++.
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.
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.
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
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
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.
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.
Source: Stackoverflow.com