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.
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:
