Forum Discussion
Excel Exponential format problem
Hi Anonymous ,
That is a limitation of excel itself as you can see in the link below excel only saves the first 15 numbers, so even if you add a xlsx you would get zeros and not the values.
On the first post you add the exponential was of 14 so everything worked properly, if you are abble to extract in text, csv or similar maybe the issue can be solved.
Super UserHi Anonymous ,
Add a new colum and do the following syntax:
try Number.ToText(Number.From([Serial])) otherwise [Serial]
This will test if the value can be converted to number if yes returns the full number otherwise returns the value
Now delete the first column and rename the second one.
It's also possible doing it without adding a new column but you need some advance techniques in power query check the video below you just need to adjust the replace to the formula I have above.
MFelix Thanks, that's a very cool trick! Unfortunately I still lose my last digits, they just turn to zeros. Ie 8,69113E+24 becomes 8691130400900000000000000.
As mentioned in another reply, I've noticed the problem goes away when open the doc and save as .xlsx. The original is a .xls. The data source is an export from an old software program and exporting as .xlsx is not an option 😞
- MFelix
Hi Anonymous ,
That is a limitation of excel itself as you can see in the link below excel only saves the first 15 numbers, so even if you add a xlsx you would get zeros and not the values.
On the first post you add the exponential was of 14 so everything worked properly, if you are abble to extract in text, csv or similar maybe the issue can be solved.