Excel – How to change the delimiter when copy-pasting from Excel

microsoft excel

I would like to copy cells from Excel and paste them into Notepad using the comma , as delimiter, instead of the tab character.

Is there any way to change the delimiter without doing a find-and-replace in Notepad or saving the spreadsheet as CSV?

Best Answer

I don't think there's any way of changing the default delimiter character (i.e. Tab) used while copying text to the clipboard from Excel. What you can do however is create a macro to achieve the result you want.

  1. Create a macro named something like CopySelectedCells and optionally assign a keyboard shortcut so you can invoke it quickly (I assigned it Ctrl+Shift+C for example):

    1

  2. From the main Macro dialog shown above, click the Edit button to open the VBA Editor.

  3. Go to Tools menu / References and click on the Browse button.

  4. Add Windows\System32\FM20.dll:

    2

  5. Select the Microsoft Forms 2.0 Object Library option now added to the Available References list:

    3

  6. Edit the macro code to look like the following:

    Sub CopySelectedCells()
    Dim str As String
    For Each rangeRow In Selection.Rows
        For Each rangeCol In rangeRow.Cells
            str = str & rangeCol.Value & ","
        Next
        str = Left(str, Len(str) - 1) & vbCrLf
    Next

    With New DataObject
        .SetText str
        .PutInClipboard
    End With
End Sub

  7. Close the VBA Editor, select a range of cells and invoke the macro, then paste in Notepad to see the comma-separated result.

Related Question