Convert Excel Numbers in "E" Format to Integers in JMP?

pmroz

I have Excel spreadsheets with an ID column that is a 12 digit integer.  The numbers appear in Excel like this: 1.5001E+11, but when I click on a cell the proper value is shown in the "cell display area", e.g. 150010277208.  When I import this spreadsheet into JMP the numbers are stripped to value like 150010000000.

The fix is to reformat the column in Excel to be a number with 0 digits after the decimal point.

Is there a way around that?  I have lots of users who have lots of spreadsheets and don't want to take the time to reformat these columns.

 

Dan_Obermiller

Easiest way that I have found still requires a reformatting. The Excel column is likely formatted as "General". If it is "Number" the problem seems to go away. I would guess making it text would also make the problem go away.

I do believe the problem is somehow with Excel. When I save the Excel file as TXT or CSV, the values are truncated as you see in JMP. That tells me that Excel is reporting only the truncated values even though they have the real value available somewhere.

 

I think your only course of action is to change the formatting in Excel.