MS Excel adds line break when copying a cell

Question

MS Excel 2010 or 2007 or any version for that matter when you copy a cell(not selecting text from the cell) and pasting to any editor adds a line break and sends the cursor to next line. Its annoying to move up the cursor back to last line.

It doesnt look much work to get the cursor back to last line but if you have to keep doing this a lot of times as a programmer i feel really bad. Any suggestions if there is a setting in excel to stop this?

I tried looking online didnt find any thing and also looked up the options of excel, didnt find anything.

Answer 1

I was copy/pasting a large file of translations for our application between the Excel spreadsheet from the translation company and our code. Having to delete the newline each time was driving me up the wall.

In the end I copied the whole sheet and pasted it into a new Google Docs spreadsheet. Then I used that for my copying needs as it does not add the newline on copy that Excel does.

Hope that helps!

Answer 2

You can create your own copy routine, that does not store the whole cell, but only its value in the clipboard:

Sub OwnCopy()
    Dim DataObj As New MSForms.DataObject
    
    DataObj.SetText ActiveCell.Value 'depending what you want, you could also use .Formula here
    DataObj.PutInClipboard
End Sub

To be able to compile the macro, you need to include "Microsoft Forms 2.0 Object Library" as a reference (in the Visual Basic editor, go to Tools->References and browse %windir%\system32 for FM20.dll¹).

You can now either assign this macro to another shortcut (e.g. Ctrl-Shift-C) using the default run macro dialog - or even overwrite Ctrl-c by executing Application.OnKey "^c", "OwnCopy". However, I'd not recommend overwriting Ctrl-c as you'll most likely want to use all the other information that usually is copied with it in any other case than pasting to an editor.

To make this permanent, just store the macro in your Personal Workbook.

In case you want a more sophisticated copy routine that can also handle multiple cells/selection areas, use this code:

Sub OwnCopy2()
    Dim DataObj As New MSForms.DataObject
    Dim lngRow As Long
    Dim intCol As Integer
    Dim c As Range
    Dim strClip As String
    
    Const cStrNextCol As String = vbTab
    Const cStrNextRow As String = vbCrLf
    
    With Selection
        If Not TypeOf Selection Is Range Then
            On Error Resume Next
            .Copy
            Exit Sub
        End If
            
        If .Areas.Count = 1 Then
            For lngRow = 1 To .Rows.Count
                For intCol = 1 To .Columns.Count
                    strClip = strClip & _
                        Selection(lngRow, intCol).Value & cStrNextCol
                Next intCol
                strClip = Left(strClip, Len(strClip) - Len(cStrNextCol)) _
                    & cStrNextRow
            Next lngRow
        Else
            For Each c In .Cells
                strClip = strClip & c.Value & vbCrLf
            Next c
        End If
        strClip = Left(strClip, Len(strClip) - Len(cStrNextRow))
        DataObj.SetText strClip
        DataObj.PutInClipboard
    End With
End Sub

^{1 In actuality this file exists at %ProgramFiles%\Microsoft Office\root\vfs\System - Office does some virtualisation trickery to make it appear where it does.}

Answer 3

I agree it's annoying, but not sure there's anything to be done.

Excel automatically formats the cells... tab-delimited columns, line-delimited rows (a single cell is a row of 1 column).

To verify: In .NET, Clipboard.GetText() shows that the formatting is already in the clipboard

Here's some IronPython to illustrate:

# Copy some cells in Excel, then run the following
import sys, clr
clr.AddReference("System.Windows.Forms")
from System.Windows.Forms import Clipboard
Clipboard.GetText()