Retaining precision when copying and pasting numeric data (floating point) from Excel to a StringGrid

[Gord P 14]

Maybe this is obvious to most or summarized better somewhere else (that I couldn't find) but here is what I have sorted out and hopefully it is a bit helpful to someone.

 

You need to be careful when allowing users to copy and paste numeric data from Excel to a StringGrid or any control that uses text as easily lose some significant digits.  It is more of an issue of how Excel pastes to the clipboard than it is a Rad Studio issue.  Excel pastes the text of what you see in a cell, not the text of the number itself.   For example, If you type in the number 123456789012 (twelve digits) into a cell, it will show up as 1.23457E+11 if your cell is wide enough - as follows.

If it isn't wide enough, Excel will drop even more digits and it may show up as 1.23E+11.  In the Formula Bar, the number still shows up as 123456789012.  Now if you copy this cell, and paste it into Notepad, you will see 1.23457E+11 (or less digits as the case may be) and NOT the original number (123456798012).  You have lost several digits of precision.

 

We'll get to how to avoid that, but first I want to mention that I chose a 12 digit number as my example because for some reason if it is less than that, Excel will show the full number in the cell if your cell is wide enough (it often autosizes it to make it wide enough).  Actually, if you include a decimal somewhere in there, it will only show 10 digits.  Excel only wants to show 11 characters in a cell: a decimal counts as a character; and "E+00" counts as 4 characters.  Interesting enough the minus sign doesn't count as a character, i.e. if you enter 112233.445566 you will see 112233.4456 in the cell.  However, if you enter -112233.445566, you will see -112233.4456 in the cell.  Don't ask me (I'm sure there are some here who understand the IEEE methods of storing numbers that can answer that).  Don't worry if you didn't catch much of this paragraph, the bottom line is that if you enter a number into a cell with too many digits you will lose some of them.

 

To make sure that your number pastes correctly, right click on the range of cells that you want to copy and select "Format Cells...".  In the Category list, choose "Scientific".  Then increase the number of decimal places to 14.  [Why 14?  Because Excel only has 15 digits of precision (typical Double precision).  You can see this if you put in 12345678901234567980 into a cell.  In the Formula bar you will see 12345679801234500000]

 

Now when you paste the number into Notepad, or your stringgrid or wherever, you will see the full precision of the number (up to 15 digits as discussed).

 

One minor drawback is that by choosing 14 digits behind the decimal in the scientific format, you will likely end up with a lot of 0's in your cell.  For example, your number may have been 1234567 but now shows up as 1.23465700000000E+06.  Those extra 0's are a waste of space in your cell.  One workaround is to use ToDouble on the string that you pasted your number into as you put it into a grid cell.  For example (c++ code sorry):

    String YourNumberAsText;
    YourNumberAsText = Clipboard()->AsText;
    YourNumberAsText.Delete(YourNumberAsText.Length()-1,2);  // need to get rid of the line feed characters that Excel appends (or tab if multiple columns)
    StringGrid1->Cells[0][0] = YourNumberAsText.ToDouble();

I recognize that this last statement is converting a string to a double and then back to a string again but it does the trick.  I was going to use FormatFloat with #.############E00 but I don't need to using ToDouble this way (i'm pretty sure anyways).

 

Let me know if this has helped you or if you think it is completely (or partially) bogus for some reason.

 

[Pat Foley 51]

My concern is this about every 15 years

 

https://interestingengineering.com/science/genes-renamed-to-stop-microsoft-excel-from-mistaking-them-for-dates

 

[Gord P 14]

I assume you are saying my post is not that relevant to many people.  Maybe.  I am sure that there are some that copy and paste floating point numbers from Excel.

[Pat Foley 51]

My concern is this about every 15 years

 

https://interestingengineering.com/science/genes-renamed-to-stop-microsoft-excel-from-mistaking-them-for-dates

 

Well, that is a deep subject ~possible reply in conversation when "Well?" is asked.

 

In XL use the extended paste methods value, ref, blah, transpose from the ribbon, or a right click when over the target cell or range to get the number and not the text.  Like a well you would dig deep into subject matter (floats integers strings) to get "water" from copy/paste schemes.

  

Edited October 4, 2023 by Pat Foley
refined what a well is

[Gord P 14]

AFAICS there is no Copy "Special" analog to Paste Special.  Paste Special in Excel is of no use in this situation because it is only applicable to copying and pasting within an Excel document.

 

If you actually know of a better way to do what I have described, please post it.  I am not saying I have posted the best solution, I have posted a solution.  I am open to a better one.