Microsoft Excel Copy paste without quotes

Question

Problem 1:

How to copy paste cell values from Excel file without copying the quotes(" & ")?

Problem 2:

Next line chars are also ignored while pasting

Excel File Text:

Text after Paste:

PS: The only way I can do it without quotes is, by first pasting value in MS Word and then paste it in text file from MS Word file.

Answer 1

Well there's nothing much you can do about it. However there is a work around for it.

You can use CLEAN function as =CLEAN(A1) (specify your cell address). Then copy the text to notepad and you'll get the desired result.

But CLEAN function also erases line breaks from text as this function removes all nonprintable characters from text.

EDIT1: _________________________________________________________________________________

This is the difference in result after copying.

EDIT2: __________________________________________________________________________________

Here is a VBA solution. Try the following code:

Sub CopyToNotepad()
    Dim objData As New DataObject  'set reference to Microsoft Forms 2.0 Object Library
    Dim strTemp As String
    Dim i As Long, FN As Integer
    Dim FilePath As String, FileName As String
    Dim MyRange As Range
    Dim cell As Variant

    FilePath = "C:\test file\"   '---> give your file path
    FileName = "test.txt"        '---> give your file name
    FileName = FilePath & FileName
    FN = FreeFile

    Open FileName For Output As #FN

    Set MyRange = Worksheets("Sheet1").Range("A3:A5")
    For Each cell In MyRange.Cells
        strTemp = Replace(cell.Value, Chr(10), vbCrLf)
        objData.SetText (strTemp)
        Print #FN, strTemp
    Next

    Close #FN
End Sub

To use the DataObject in your code, you must set a reference to the Microsoft Forms 2.0 Object Library.

This will give you output like below image: