UKH

Hi Folks,

I've been copying and pasting date into Excel, and have come across something odd, where it happens that most of the time when I control C, then V, the data appears as vertically arranged, but occasionally it simply comes out in one continuous horizontal stream across the top of the sheet.

Does anybody know why this happens, and what needs to be done to be able to choose which one happens?

It's Bloody Annoying when a stream of horizontal data appears at the top of the sheet - as if randomly...

Thanks.

Where are you copying from?

Defra's data website, or the segment of defra which has air quality data in it.

Rather than use Ctrl V you can select one of the Paste Special options from the toolbar/ribbon/whatever it's called. You may think 'paste' is simple - there's lots more options than you might think: Transpose, Values etc etc etc. At least 20 plus God knows how many permutations.

Obviously you're also not taking into account the Excel 'Random' feature, which is something introduced in about 2000 when Microsoft finally lost control of the product and accepted that 'sh*t happens'. Word has the same feature. 

Excel is not finding anything it recognizes as a linefeed in what is on your clipboard so it's pasting it as a line of text.

You can use "Text to Columns" and set your own delimiter to convert the line into a series of horizontal cells, then copy and paste special + transpose to get the horizontal cells into a vertical column - a PIA but not too bad if you don't have to do it too often.

Thanks, do you know why would it would suddenly start to happen, and not happen all the time with the same 'block' being copied and pasted?

What I've been copying and pasting has been columns of figures with headings, and most of the time it's worked, until it didn't, and then did again.

It partly depends on exactly what you select. There are browser plugins which make it work more reliably. “Table Capture” is one which I use in Chrome. 

Check when you select the table to copy you're not overrunning by a character on the bottom row. You might be able to copy one row at a time more reliably

Difficult to say, assuming you are doing exactly the same each time.

Make sure you copy entire rows in the source table - don't miss cells at the start or end.

Other possibilities that could cause this error are number formatting in the source table - do the numbers sometimes contain spaces, eg: 1 million written 1 000 000 ?

Potentially, empty cells could cause errors, but normally just cause some cells to be shifted to the left.

Sounds like you are copying carriage returns or other line control functions on some occasions and  they are missing on others.  I would tidy up in Word first.

I’ve had a similar issue on the coronavirus.data.gov.uk site. Right click and select paste special and then select paste as text. 

If you do this sort of thing a lot, paste it into notepad++ first. It comes with lots of different line end variants and allows you to see what the format is. Free download and very useful for all sorts of cross platform stuff.

If at all possible, save your data source to disk and then import it with the “File > Import” menu.  It’s less error prone than copying and pasting random stuff in to a spreadsheet as you’re manually and visually led through how Excel converts the data to a 2D grid layout.

 Better to download the csv from defra and import to excel

If you press ctrl-z, select the cell you want to paste into again, and press ctrl-v again does it paste correctly second time round?

If so, it is just the excel temperament "feature". It sometimes switches the focus to a completely different cell to paste into as well.

That coincided with “Clippy” didn’t it?  

“It looks like you’re trying to analyse a pandemic...”

Without wishing to do "grandmother" and "eggs", when you have selected the data & copied you want to import (i.e. put it in the copy/paste buffer), it can make a difference when you paste whether you have selected the cell you want to paste into for input, or selected the formula bar for input IYSWIM.

There is an option to have it sent to an email, but that isn't so reliable, it worked the first couple of times, and then it didn't. I'm nearly done now at any rate.

Just a general comment. I have to use the MS Office suite at work but I choose to use Libre Office at home. It's compatible with international document standards & MS too (not always the same). It seems to do what I want rather than what it wants a lot more & I hardly ever feel messed about by it. I generally pay them about a tenner a year which is a bargain. You get a very usable drawing program with it too.

Ten pounds a year for software which does what you want it to sounds like good value indeed, for during those moments of looming deadlines. 

If you paste the errant copies into Word you might be able to see what's wrong with it