Leave out quotes when copying from cell

Question

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  amp CHAR 9  amp  SOME NOTES FOR LINE 1   amp CHAR 9  amp  2  amp CHAR 9  amp  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

User · Answer

Please use the below formula   Clean  1  amp CHAR 9  amp  SOME NOTES FOR LINE 1   amp CHAR 9  amp  2  amp CHAR 9  amp  SOME NOTES FOR LINE 2      and you will get what you want  -

User · Answer

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  quot Quick Acces Folder quot  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  quot ctrl c quot  as a hotkey to it   Hotkey assignment   Developer tab  Macros  click the macro  options  then put the letter  quot c quot  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    quot  quot        On Error GoTo 0     SetClipboard  strClip  End Sub  This will still need for you to build the functions  quot SetClipboard quot  and  quot GetClipboard quot   Below we have the definition of the  quot SetClipboard quot  and  quot GetClipboard quot  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  quot User32 quot   ByVal hWnd As LongPtr  As LongPtr     Private Declare PtrSafe Function EmptyClipboard Lib  quot User32 quot     As LongPtr     Private Declare PtrSafe Function CloseClipboard Lib  quot User32 quot     As LongPtr     Private Declare PtrSafe Function IsClipboardFormatAvailable Lib  quot User32 quot   ByVal wFormat As LongPtr  As LongPtr     Private Declare PtrSafe Function GetClipboardData Lib  quot User32 quot   ByVal wFormat As LongPtr  As LongPtr     Private Declare PtrSafe Function SetClipboardData Lib  quot User32 quot   ByVal wFormat As LongPtr  ByVal hMem As LongPtr  As LongPtr     Private Declare PtrSafe Function GlobalAlloc Lib  quot kernel32 dll quot   ByVal wFlags As Long  ByVal dwBytes As Long  As LongPtr     Private Declare PtrSafe Function GlobalLock Lib  quot kernel32 dll quot   ByVal hMem As LongPtr  As LongPtr     Private Declare PtrSafe Function GlobalUnlock Lib  quot kernel32 dll quot   ByVal hMem As LongPtr  As LongPtr     Private Declare PtrSafe Function GlobalSize Lib  quot kernel32 quot   ByVal hMem As LongPtr  As Long     Private Declare PtrSafe Function lstrcpy Lib  quot kernel32 dll quot  Alias  quot lstrcpyW quot   ByVal lpString1 As Any  ByVal lpString2 As Any  As LongPtr  Else     Private Declare Function OpenClipboard Lib  quot user32 dll quot   ByVal hWnd As Long  As Long     Private Declare Function EmptyClipboard Lib  quot user32 dll quot     As Long     Private Declare Function CloseClipboard Lib  quot user32 dll quot     As Long     Private Declare Function IsClipboardFormatAvailable Lib  quot user32 dll quot   ByVal wFormat As Long  As Long     Private Declare Function GetClipboardData Lib  quot user32 dll quot   ByVal wFormat As Long  As Long     Private Declare Function SetClipboardData Lib  quot user32 dll quot   ByVal wFormat As Long  ByVal hMem As Long  As Long     Private Declare Function GlobalAlloc Lib  quot kernel32 dll quot   ByVal wFlags As Long  ByVal dwBytes As Long  As Long     Private Declare Function GlobalLock Lib  quot kernel32 dll quot   ByVal hMem As Long  As Long     Private Declare Function GlobalUnlock Lib  quot kernel32 dll quot   ByVal hMem As Long  As Long     Private Declare Function GlobalSize Lib  quot kernel32 quot   ByVal hMem As Long  As Long     Private Declare Function lstrcpy Lib  quot kernel32 dll quot  Alias  quot lstrcpyW quot   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    amp H2     Const GMEM ZEROINIT As Long    amp H40     Const CF UNICODETEXT As Long    amp HD     OpenClipboard 0 amp      EmptyClipboard     iLen   LenB sUniText    2 amp      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 amp      OpenClipboard 0 amp      If IsClipboardFormatAvailable CF UNICODETEXT  Then         iStrPtr   GetClipboardData CF UNICODETEXT          If iStrPtr Then             iLock   GlobalLock iStrPtr              iLen   GlobalSize iStrPtr              sUniText   String  iLen   2 amp  - 1 amp   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

User · Answer

It s also possible to remove these double-quotes by placing your result on the  Clean  function   Example    CLEAN  1  amp CHAR 9  amp  SOME NOTES FOR LINE 1   amp CHAR 9  amp  2  amp CHAR 9  amp  SOME NOTES FOR LINE 2      The output will be pasted without the double-quotes on other programs such as Notepad

User · Answer

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 - gt  REFERENCES  amp  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 amp   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 amp       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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    amp  strFilename  amp    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

User · Answer

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

User · 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

User · Answer

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

User · Answer

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

[excel] Leave out quotes when copying from cell

Examples related to excel

Examples related to clipboard