HELP - PLEASE - formula to convert scientic to text

[petersd]

Can someone help me write a formula to take a column of scientific notation and convert to proper text format? For some reason when I saved the data to CSV some of the data saves wrong in scientic notation.

In the 2nd column you see I have manually converted the first column to the proper format, basically you have to remove the decimal, drop the zero and add 1 to the value of the last number. Can anyone help??? PLEASE!!!!!!!!!!


3.20E-05 32E-6
3.20E-05 32E-6
3.20E-06 32E-7
3.20E-06 32E-7
3.20E-07 32E-8
3.20E-07 32E-8

 

[hatman]

Have you tried converting to text BEFORE you try to export, rather than exporting values? assuming your first value is in cell A1, you could use the formula =text(A1,"00E+0") and copy it down as required. Then you copy the result, and Paste->Special->Values back to the original location and delete the column of equations. Try it on a test database and see if it gets you going in the right direction.

 

[petersd]

Thanks, the problem is guess resides within the docbase as possibly different formats so when it displays to extra to csv its already in the wrong format. only about 4percent goes funny, the rest is fine. I then save the open data locally. Given the format I require is there a formula to convert ltes say 3.00E-11 to 30E-12? some fo the number are like 3.00E-06 so it would have to change to 30E-7. Does this help? I do not have access to check or change the clients database so a formula is my only option. Any help would be greatly appreciated!!!!!!!!!!!!!!!!!!!!!!!