Search code examples
excelclipboard

Leave out quotes when copying from cell

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?

Solution

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