4

I would like to manually copy data from non-adjacent Excel rows (or columns) and paste it into a text editor.

So far I haven't found a way to do this efficiently. (I'm using Excel Home/Student 2013.)

For example:

  • In Excel, select (Ctrl+LeftMouseClick) rows 1, 5, 8, and 10
  • Copy (Ctrl+C)
  • In the text editor, paste (Ctrl+V) - you'll get ALL the data from rows 1 to 10

Cumbersome workaround so far:

  • In Excel, select (Ctrl+LeftMouseClick) rows 1, 5, 8, and 10
  • Copy (Ctrl+C)
  • Paste (Ctrl+V) somewhere else in Excel (e.g. empty extra sheet). You'll get 4 adjacent rows with data only from rows 1, 5, 8, and 10
  • Copy (Ctrl+C) again
  • In the text editor, Ctrl+V - you'll get the data from rows 1, 5, 8, and 10

Unbelievable that this seems not to be possible after more than 30 years of Excel development. Is there a reason why one can't do this directly?

Is there maybe nevertheless a trick to do this more efficiently?

Any ideas?

5
  • @robinCTS, clearly your edited formattig looks better, but why did you remove "Excel" from the title? Because it is in the tags?
    – theozh
    Commented Jul 21, 2018 at 6:00
  • Well, I thought we were supposed remove all the tags from the title. Thanks to your bring it up, I did a bit of research and it looks like I was wrong! Duplicating the tags in the title is fine, as long as they are worked into the title organically and conversationally. Looks like I'll have to stop doing it :-$ Once again, thanks for bringing the issue to my attention.
    – robinCTS
    Commented Jul 21, 2018 at 6:35
  • Hmm. More research has turned up more posts which I now remember reading a long time ago, and which led me to always remove all the tags. It seems like there's no consensus. On the one hand is the camp advocating the complete removal of all the tags (in particular the programming language ones), and on the other is the group saying tags used organically are fine. IIRC last time I gave up researching. Maybe I'll try to dig deeper/look for newer posts this time ;-)
    – robinCTS
    Commented Jul 21, 2018 at 6:53
  • As detailed here I think this should work for copying outside of Excel, but I can't test until tomorrow to be sure. If someone else can test it sooner and confirm if it works then feel free to post as an answer.
    – Xantec
    Commented Jul 22, 2018 at 13:33
  • @Xantec, ok, it says: select your cells, press Alt+; and then Ctrl+C. With this it should not copy hidden cells. However, as I commented to the answer of Aganju below, Excel2013 seem to not copy hidden cells anyway. So, this shortcut seems to be useless at least for Excel2013. Independent of that, hiding unwanted rows first and make them again visible afterwards would be definitely more extra effort.
    – theozh
    Commented Jul 22, 2018 at 18:31

2 Answers 2

2

It is an intentional 'feature'.

The hidden/unselected data is copied too, because gazillions of users have complained in the previous decades that they missed some data because some filter was still on and they didn't see it.

Personally, I agree with your desires - I have the same issue all the time. But we have to live with the limitations of millions of users that are not so sure what they are doing, therefore it is implemented this way.

2
  • Oh, it's intentional? But why does it work with this intermediate copy&paste step within Excel? Btw, just tested, when I hiding/filtering the unwanted rows (e.g. 2,3,4,6,7,9) I can copy the desired data directly to a text editor. But hiding/filtering is even much more effort than just selecting the wanted rows or via the itermediate copy&paste step. Pretty inconsistent and painful!
    – theozh
    Commented Jul 21, 2018 at 5:41
  • FWIW, LibreOffice also can't do it. But there the result is different. I get only the first selected row in the text editor followed by empty cells (1024 cells in total). Again also pretty strange.
    – theozh
    Commented Jul 21, 2018 at 6:08
0

The reason is a little deeper.

There are many things one wants a spreadsheet to do involving the copying or transferring of information in, into, and out of, it. Sometimes "just because" and in any spreadsheet, sometimes in a particular spreadsheet due to its program's features, or even choices one has himself made. And Excel supports some/many of these things.

However, the information it needs to carry with the data, as part of the data/formatting/etc., or to enable the full range of uses, INSIDE Excel that you may wish to put it to, is very often far more than the Windows Clipboard can have and still be "true" to its own conception.

The Clipboard does not really distinguish between one bit of textual information representing data Excel might place in it and textual information representing, perhaps, formatting, that Excel might place in it. Formatting, in particular, would be partially supported, partially not. The Clipboard will pass font information for example, and likely has a way to know a bit of text is that kind of thing. Excel can pass its own Copy-ing to it in the required form. But maybe the Clipboard does not have a way to mark "Center Across Selection" or "Merge Cells" information in a standard way that other programs can recognize and use properly or discard.

Excel must keep that information in its Copy-ing, so as to be able to use it in its own operations. But it cannot at the same time be passed to the Clipboard without it becoming weird and stray text added to the actual desired text. (The converse/corollary/whatever of that is seen in copying a cell to Paste its contents into, say, a website's login entry boxes and Excel having added something to cause a carriage return/line feed to be added that may or may not be ignored.)

So Excel uses its own internal Clipboard. It takes whatever "true" Copy Excel considers having been made, with any information it feels it might need, and holds it until one Pastes. If pasting inside Excel, and often in other Office programs, though often not, the information can be used fully. In Excel, one might see the program extract the two cells' data and formatting that you selected using Ctrl-Click and decide how it should place it where you are pasting it. So you get the two cells and their formatting with the simple paste, and while it COULD have been written to keep their relative locations, say rows 2 and 6, they decided such a thing should be pasted as if originally contiguous. Well... choices. (Useful to me as often as aggravating. I imagine that varies for others.)

At the SAME time, Excel writes to the Windows Clipboard (distinguished from its own internal "clipboard") what the programmers figured should (and can) be written to it. They did not necessarily think of every variation on every theme. In your example, they clearly figured uses outside Excel would be better served with all the contiguous data marked by the start and stop cells. Or that is, and/or was, the standard thought at the time. Or it was never thought of at all. So many possibilities.

(This internal/intermediary "clipboard" is, by the way, the source of difficulty that leads to the weird error message about how there is an error with the clipboard but you can still paste into other programs.)

So it's likely in no way a "bug" but rather a chosen feature, an incidental feature, or a thing that was never thought of so can't be right ("feature) OR wrong ("bug).

Clearly though, Excel is aware that if a row, say, is hidden/filtered out/set to 0 height is undesired in a Paste, even one pasted outside Excel itself. Maybe the thinking was that a "positive step" to indicate those things were undesired was good enough to send a data package to the Clipboard that does not include such. All those situations might actually be achieved by each of them setting the row height to 0, and so maybe Excel looks for that characteristic. The use of Ctrl-Click to mark particular cells for copying, coming years and years later, might simply not activate that removal before writing to the Clipboard. Might be as simple as that.

In any case, you might make an entry on the current UserVoice web tool. For a veyr long time it looked like Excel people just made nice noises, then completely ignored any entry. But recent changes suggest that they did pay attention, just that it takes 10-20 years for them to act upon them. (Sigh...) But one never knows. That place IS the official place for such suggestions though, unless your employer (or you) pay tons of money to be on a support plan.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .