20% of Scientific Papers On Genes Contain Conversion Errors Caused By Excel, Says Report 2

[drawoh]

This is an interesting article on Slashdot.

20% of Scientific Papers On Genes Contain Conversion Errors Caused By Excel, Says Report. The link is to Slashdot.

--
JHG

 

[IRstuff]

Text to Columns in Excel 2007 results as expected, when everything is set to text

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529

 

[SkipVought]

Yes!!!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

You and I are talking about two completely different things it appears. The Text to column wizard, Excel 2013, has THREE steps, not SEVEN. What feature are you using?

Skip - your post of 11:55, 16-Sep has 7 steps!

But if I follow the process with the table from the linked pdf in Excel 2016 it works the same as shown by IRStuff above. The only difference from your post is I get 7 rows of data in a single column when I paste, not everything in one cell.

Another point is that if you go through the text to columns process then paste the same data somewhere else, this time it will be pasted in columns, but it doesn't remember the format setting, so the fractions come in as mixed date and text. You have to set the format to text over the whole range of the table before pasting, then it will paste as text in columns without going through the text to columns process. This happens even if you re-copy the data, or open a new workbook and paste there. As far as I can see the only way to make it forget and go back to pasting in a single column by default is to close Excel and re-start.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

Oops . My bad for mixing my steps as numbers with the Text to column wizard which has numbers.

xwb, I am sorry for causing this confusion.

Doug, thanks for pointing this out to me.

I am puzzled why my copy/paste produces one long string without any line breaks when I paste into Excel.

I did paste the table here and then copy/pasted into Excel and that produced 7 cells in one column, from which I performed the Text to columns.

But I'm still very interested in understanding why the process fails for xwb. xwb, if you would run my procedure and report the results, it would help my understanding.

BTW, before pasting your table into Excel, 1) ENTER any value into A1, 2) SELECT A1, start the Text to columns wizard and DESELECT Space in Step 2 of the DELIMITED option and FINISH.

Then paste in your table in A1. Now you're ready to do the parsing.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IRstuff]

The string problem in Excel is because it doesn't recognize the newline character that the pdf uses. Other programs, like this browser, or notepad++, are able to interpret the string as we see it in the pdf. The converse is often required. I often copy article titles from pdfs to use as their file names, and windows explorer does recognize the new lines, and I have to paste into Chrome or Internet Explorer to get rid of the new line break.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529

 

[xwb]

It is probably a UK-US locale-date problem. I guess you may get a similar problem with Japanese/Chinese style dates which are yy/mm/dd. You won't get that many problems running from a US locale.

I have found that, even with macros, sometimes Excel just ignores the locale and assumes US until the 13th, then it switches to UK. I've had macro generated spreadsheets which copy from IE where the first 12 days of the month get switched to US format and the rest come out in UK format. The only way I've found that always works is to preset the format of the column before writing to it.

 

[SkipVought]

Still trying to understand what's going on.

Here is a list of various ways I have manually entered 12-1/2, which appears to represent a numerical value 12 1/2 or 12.5 in the subject table. In the case of entering an intended fraction I entered 12 1/2 (with a SPACE)

[pre]
Preset Entered Display Actual
Format Value Value Value
Fract'n 12-1/2 12/1/2002 37591
Fract'n 12 1/2 12 1/2 12.5
Text 12-1/2 12-1/2 12-1/2
[/pre]

The only preset number format that "sticks" is TEXT. Presetting the format as Fraction is overridden by Excel's 'rules' for date entry/conversion. 12-1/2 or 12/1-2 will both result in a date regardless of presetting the cell number format.

Can anyone else, on the east side of the pond, enter 12-1/2 to result in a fraction other than presetting the format to TEXT which results in no fraction at all but rather a visual to be represented as a fraction.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

Skip - with Australian English settings it works the same as shown in your post, except the date number is 37268 (12-Jan-2002).

I'm not sure that allowing 12-1/2 to be entered as a number is a good idea, because if entered anywhere other than the first value in an equation its value should be 11.5, not 12.5.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

The intention of the table in the PDF is a fraction: 12 1/2, although there's a DASH in there. So to prevent a date conversion, it must be entered in a cell with TEXT number formatting.

So you are stating that the date conversion rules in Australian English settings (and most probably in Great Britain as well), assumes MDY, which I now admit, shoots down my assumption that the conversion is always MDY. So Excel is smarter than I thought, which is better. I want to modify my FAQ to reflect conversion in accordance with regional settings.

My angst is trying to reconcile xwb's experience.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

In the discussion above Skip said that when you copy and paste from a pdf, the text is pasted as a long string in a single cell, but I found that it was pasted in rows, as in the original table.

That was last week.

This morning I found my system had been updated overnight, and when I did the same process, with the same file, I found the text pasted as a long string in a single cell.

Strangely there seems to be nothing about this anywhere on the Internet, at least I couldn't find it, but with some experimenting I found:

[ol 1]
[li]It's not an Excel thing. The same table pasted into a text editor also appeared as a single cell.[/li]
[li]I then noticed the pdf file had not opened in Adobe Reader, it was opened in Edge. It seems that Microsoft think they know better than I do about what my default programs should be.[/li]
[li]Switching back to Adobe Reader the copy and paste worked as before, pasting as 7 rows.[/li]
[/ol]

So the message is, if Windows does an automatic update, check your file associations, particularly for opening pdfs.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/