Leave quotation marks when copying from a cell

Question

Problem :
When you copy a cell from Excel outside the program, double quotes are automatically added.

More details :
I am using Excel 2007 on a computer running Windows 7. 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 a number) looks like this in Excel:

 1SOME NOTES FOR LINE 1.2SOME NOTES FOR LINE 2. 

Well, good. But, if I copy the cell to another program, such as notepad, I get annoying double quotes at the beginning and end. Note that the tabs created by "CHAR (9)" are saved, which is good.

 "1 SOME NOTES FOR LINE 1. 2 SOME NOTES FOR LINE 2." 

How can I save these double quotes when I copy to another program? In other words, can I save them from automatically adding when the cell is copied to the clipboard?

Answer 1

If you try to paste into Word-Pad, Notepad ++ or Word, you will not have this problem. To copy the value of a cell as plain text, in order to achieve what you described, you need to use a macro:

In the workbook where you want this to apply (or in your Personal.xls if you want to use multiple books), put the following code in the standard module:

the 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 VBE with Alt + F11 , and then right-click on your book in the upper left window of the project and choose Insert> Module. Paste the code into the code module window that opens on the right.

Return to Excel, go to Tools> Macro> Macros and select the CopyCellContents macro, and then select Options in the dialog box. Here you can assign a macro to a shortcut key (for example, like CTRL + C for a regular copy) - I used CTRL + Q.

Then, when you want to copy one cell to notepad / anywhere, just do Ctrl + q (or whatever you selected), and then do CTRL + V or Edit> Paste at your destination.

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

EDIT : (from comments)

If you did not find the Microsoft Forms 2.0 library in the list of links, you can try

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

Answer 2

I just had this problem, and packing each cell using the CLEAN function fixed it for me. This should be relatively easy to do by doing =CLEAN( by selecting a cell and then autofilling the rest of the column. After that, inserts into Notepad or any other program no longer have duplicate quotes.

Answer 3

Paste it into Word first, then you can paste it into notepad and it will appear without quotes

Answer 4

If you want to select multiple cells and copy their values ​​to the clipboard without all these annoying quotes, the following code may be helpful. This is an improvement on the above code 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 

Answer 5

My solution, when I got into the quotation mark problem, was to remove the carriage from the end of the text of my cell. Because of these carriage returns (inserted by an external program), Excel added quotation marks to the entire line.

Answer 6

Possible problem regarding the response from user user3616725:
Im on Windows 8.1 and there seems to be a problem with the associated VBA code with the 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:
By executing over the code and pasting the clipboard into the cell in Excel, I get two characters consisting of squares with a question mark inside, for example: ⍰⍰. Paste in Notepad does not even show anything.

Decision:
After quite a long search, I found another VBA script from the user "Nepumuk" who uses the Windows API . Here is 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 as the first VBA code above, change the Sub "Beispiel ()" to:

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

To:

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

And run it through the Excel macro menu, as suggested from "user3616725" from the accepted answer:

Go back to Excel, go to Tools> Macro> Macros and select a macro called CopyCellContents, and then select Options in the dialog box. So you can assign a macro to a shortcut key (for example, Ctrl + c for normal copy) - I used Ctrl + q.

Then, when you want to copy one cell to notepad / anywhere, just do Ctrl + q (or whatever you selected), and then do Ctrl + v or Edit> Paste to the selected destination.

Edit (November 21, 2015):
@ comment from "dotctor":
No, this is not a serious new question! In my opinion, this is a good addition to the accepted answer, as my answer solves the problems that you may encounter when using the code from the accepted answer. If I had a great reputation, I would create a comment.
@ comment from "Teepeemm":
Yes, you are right, the answers beginning with the heading "Problem:" are misleading. Changed: “Possible problem regarding the response from user user3616725”: “. Of course, as a comment, I would write much more compact.

Answer 7

"If you want to select multiple cells and copy their values ​​to the clipboard without all these annoying quotes" ( without errors in solving several Peter Smallwood cells), "the following code may be useful." This is an improvement on the above code from Peter Smallwood (which "is an improvement on the above code from user3616725"). This fixes the following errors in Peter Smallwood's solution:

• Avoids "Variable not defined" Compiler error (for "CR" - "clibboardFieldDelimiter" here)
• Convert an empty cell to an empty string with "0".
• Add a tab (ASCII 9) versus CR (ASCII 13) after each cell.
• After each line add CR (ASCII 13) + LF (ASCII 10) (compared to CR (ASCII 13)).

NOTE. You will still not be able to copy the characters embedded in the cell, which will lead to the exit from the target field into which you paste this cell (for example, Tab or CR when pasting an access table or SSMS into the editing window).

---

 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 (ie 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 

Answer 8

To save line breaks when pasting into a notebook, replace this line with a macro:

 strTemp = ActiveCell.Value 

by:

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

Answer 9

Please use the formula below

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

and you get what you want; -)

Answer 10

You can also remove these double quotes by setting your result to the Clear 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 inserted without double quotes into other programs such as Notepad ++.

Answer 11

You can do this in an Excel macro via VBA by 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 want to get the file names and contents in multiple files, here is a short snippet that avoids 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 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 

Answer 12

This strange problem happened to me when I put CHAR (9) at the beginning of the formula: = + char (9) & "case" & char (34) & B195 & "txtAux =" "" & F195 & ":" & C195 & char (34) when copying and pasting it duplicated all double quotes "case 10 txtAux =" "Estado compresor: Parado" "" in Excel it showed that case 10 txtAux = "Estado compresor: Parado", removing char (9) , solved a problem.