Exporting paramater values to excel retaining precision of number

Excel always thinks it's being helpful stripping off these zero values, Ugh.

In Excel, the only way to get the trailing or leading zeros to be left in tact is to format that data as text. Sorry.

Storing as text may be one solution, or it may cause other problems.

Question: for the purposes of your analysis, what is the difference between 13 and 13.00 and 13.0000 and 13.000000000000000? Numerically, these four values are all really the same value. Same as 0.12=0000.120000=0.12000=0.12000000=1.2E-1.

IMO, before devising a solution, we need you to explain the "significance" of these insignificant 0's.

Originally Posted by angusmdmclean

The purpose of maintaining the precision to 13.00 in Excel is that the stripping to 13 is raising question from quality assurance regarding the integrity of the data. Additionally it is very labor intensive for me to put the 0 back in again in the cells. I have different parameters in different columns and sometimes I require 1 decimal or it could be 4. So I have a lot of manual reformatting to do and it is also labor-intensive.

Unfortunately, you're SOL unless you want to resort to using VBA either to import the data or to format the data after importing it.

When opening or importing a CSV (or ".txt") file, Excel always formats cells as General, with some exceptions for special numeric forms that Excel recognizes, such as dates, times and percentages.

The General format determines how many decimal places to display based on the value and the column width. In particular, it never displays trailing fractional zeros.

Without your providing some data to demonstrate the file structure, it is not feasible for us to implement a VBA procedure to import or reformat the data.

If you want to go in that direction, it would be best if you upload an example CSV file (not the Excel file) to file-sharing website, and post the pubic/share URL here.

[ERRATA] I forgot that this forum allows us to attach files; and the moderators prefer that. Click on Go Advanced to find the Attach icon.

Alternatively, you might simply describe the file structure, if it is simple enough to describe.

I would suggest that the VBA import the data from the CSV file, instead of trying to reformat the data after Excel reads or imports the CSV file. Based on your description, I suspect that the VBA code needs to see the original form of the data in the text file in order to infer the correct cell format to use.

Is the end goal to consistently show 4 significant digits (13.00, 0.1200, 1.450, 240.0)? In the pharmaceutical industry, are your readers familiar enough with scientific notation to be able to read 1.300E1, 1.200E-1, 1.450E0, 2.400E+2? Formatting the destination cells as 0.000E+0 will consistently show four significant digits in scientific notation.