How can I control decimal places when exporting Data Collector data into Excel?

johnb

I am measuring the area of particles in a field using Data Collector to aggregate the individual occurrences. I have the Measurements Options set to 5 decimals.  When I aggregate using the Collect button, the Data Collector Table shows values at 3 decimals. When I export the Collector Table to Excel, the values show up in 9 decimal places!  When I copy and paste these values to a 2nd spreadsheet, I am seeing 14 significant digits!  While I can control the 2nd spreadsheet by formatting the cells, how can I stop the apparent proliferation of decimal places?   Is this a function of the Data Collector Export procedure or is it an Excel (2013) issue?  I am using Image Pro Premiere v 9.1.4 build 5638 64-bit.  Thank you!

johnb

Thanks Yuri- That last option worked.  Pre-formatting the spreadsheet in Excel before the export does not work.

YuriG

Hi John,

The number of decimal places in Data Collector is set in the Application options, Display page.

The numbers are sent to Excel will full precision, you can control the display format of these numbers using Excel tools, check Number format options on the Home tab of Excel.

Yuri

johnb

Would I control the decimals exported to Excel by first opening Excel and then specifying; before I export the data?  For my uses, the exported data is "raw data" which cannot be modified after it has been exported from Data Collector.

YuriG

You can try that, you can open Excel and set format of columns where the data is expected to Numeric with given number of decimals, then export data. You can also use pre-formated Excel templates.

There is also "Style" option on the Export page of the application options. When it's activated, the data is formatted as in Data Collector table. (though the export is a little slower for large tables)

Yuri