locked
Excel add quotes when copy/paste text in notepad RRS feed

  • Question

  • When i copy text from excel cell into notepad the text is surrounded with quotes. Is it possible to format the cells in a way when i copy text from them the text not to be surrounded with quotes when pasting it in notepad? I am using excel 2007.          


    Thanks!

    Tuesday, May 3, 2011 3:10 PM

Answers

  • Can you copy from the formula bar (even if you have to convert a formula to
    value before the copy)?

    Another suggestion saved from a previous post:

    I used the PutInClipboard routine that Chip Pearson has:
    http://www.cpearson.com/excel/clipboard.htm

    With this sub:
    Option Explicit
    Sub testme()

        Dim MyDataObj As DataObject
        Set MyDataObj = New DataObject

        MyDataObj.SetText ActiveCell.Text
        MyDataObj.PutInClipboard

    End Sub

    And then pasted (manually) into NotePad.  No double quotes were inserted.  But I
    did see a little square where the alt-enter was.

    Chip has instructions that you have to follow (including the tools|references
    with "Microsoft Forms 2.0 object library") on that sheet.

    =========

    If you wanted to copy multiple cells, this may help you:

    Option Explicit
    Sub testme()

        Dim MyDataObj As DataObject
        Dim myCell As Range
        Dim myRow As Range
        Dim myRng As Range
        Dim myRowStr As String
        Dim myStr As String

        Set MyDataObj = New DataObject

        Set myRng = Selection.Areas(1)

        myStr = ""
        For Each myRow In myRng.Rows
            myRowStr = ""
            For Each myCell In myRow.Cells
                myRowStr = myRowStr & vbTab & myCell.Text
            Next myCell
            myRowStr = Mid(myRowStr, Len(vbTab) + 1) 'get rid of leading vbtab
            myStr = myStr & vbCrLf & myRowStr
        Next myRow
        myStr = Mid(myStr, Len(vbCrLf) + 1) 'get rid of leading vbcrlf (2 chars!)

        MyDataObj.SetText myStr
        MyDataObj.PutInClipboard

    End Sub

    If you're new to macros:

    Debra Dalgleish has some notes how to implement macros here:
    http://www.contextures.com/xlvba01.html

    David McRitchie has an intro to macros:
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    Ron de Bruin's intro to macros:
    http://www.rondebruin.nl/code.htm

    (General, Regular and Standard modules all describe the same thing.)

    Emil, Dilov wrote:


    *When i copy text from excel cell into notepad the text is surrounded with quotes. Is it possible to format the cells in a way when i copy text from them the text not to be surrounded with quotes when pasting it in notepad? I am using excel 2007.  *

    Thanks!

    --

    Dave Peterson

    Thursday, May 5, 2011 12:02 PM

All replies

  • Hi,

    Are You doing it in code (VBA) or manually? If code please post it here.


    Kind regards, Dennis (http://xldennis.wordpress.com/)
    Wednesday, May 4, 2011 8:46 PM
  • Can you copy from the formula bar (even if you have to convert a formula to
    value before the copy)?

    Another suggestion saved from a previous post:

    I used the PutInClipboard routine that Chip Pearson has:
    http://www.cpearson.com/excel/clipboard.htm

    With this sub:
    Option Explicit
    Sub testme()

        Dim MyDataObj As DataObject
        Set MyDataObj = New DataObject

        MyDataObj.SetText ActiveCell.Text
        MyDataObj.PutInClipboard

    End Sub

    And then pasted (manually) into NotePad.  No double quotes were inserted.  But I
    did see a little square where the alt-enter was.

    Chip has instructions that you have to follow (including the tools|references
    with "Microsoft Forms 2.0 object library") on that sheet.

    =========

    If you wanted to copy multiple cells, this may help you:

    Option Explicit
    Sub testme()

        Dim MyDataObj As DataObject
        Dim myCell As Range
        Dim myRow As Range
        Dim myRng As Range
        Dim myRowStr As String
        Dim myStr As String

        Set MyDataObj = New DataObject

        Set myRng = Selection.Areas(1)

        myStr = ""
        For Each myRow In myRng.Rows
            myRowStr = ""
            For Each myCell In myRow.Cells
                myRowStr = myRowStr & vbTab & myCell.Text
            Next myCell
            myRowStr = Mid(myRowStr, Len(vbTab) + 1) 'get rid of leading vbtab
            myStr = myStr & vbCrLf & myRowStr
        Next myRow
        myStr = Mid(myStr, Len(vbCrLf) + 1) 'get rid of leading vbcrlf (2 chars!)

        MyDataObj.SetText myStr
        MyDataObj.PutInClipboard

    End Sub

    If you're new to macros:

    Debra Dalgleish has some notes how to implement macros here:
    http://www.contextures.com/xlvba01.html

    David McRitchie has an intro to macros:
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    Ron de Bruin's intro to macros:
    http://www.rondebruin.nl/code.htm

    (General, Regular and Standard modules all describe the same thing.)

    Emil, Dilov wrote:


    *When i copy text from excel cell into notepad the text is surrounded with quotes. Is it possible to format the cells in a way when i copy text from them the text not to be surrounded with quotes when pasting it in notepad? I am using excel 2007.  *

    Thanks!

    --

    Dave Peterson

    Thursday, May 5, 2011 12:02 PM
  • Thanks, for the help! 
    • Proposed as answer by Wlv2008 Friday, June 15, 2012 4:43 PM
    • Unproposed as answer by Wlv2008 Friday, June 15, 2012 4:43 PM
    Thursday, May 5, 2011 12:16 PM
  • I noticed that if your cell content contains a formula with carriage returns in it, excel will paste the result into notepad surrounded by quotes. If you remove the carriage returns from the cell content, it will paste as a single line without the quotes.
    • Proposed as answer by Jeff Owens Monday, December 16, 2013 6:09 PM
    Friday, June 15, 2012 4:45 PM