CODEWITHSUNDEEP
excelexcel-formula
Kylo Ren
Kylo Ren

Reputation: 8823

Microsoft Excel Copy paste without quotes

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:

Excel Text

Text after Paste:

Pasted Text

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.

Upvotes: 6

Views: 18835

Answers (1)

Mrig
Mrig

Reputation: 11702

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

enter image description here

This is the difference in result after copying.

enter image description here


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: enter image description here

Upvotes: 4

Related Questions