Convertic scientific notation to text format

[gcefaloni]

Hi,

I'm trying to convert the following string (135087E67) to text but excel keeps returning me scientific notation instead. All I get is 1.35087E+72.
How can I force Excel to convert it as text and keep it as 135087E67 and not convert it to scientific notation once I create a .csv file from that table that includes the above string in question.

Thank you!

Gabriel

 

[joeu2004]

The problem I'm getting isn't so much about truncating values and losing decimals

For your example, I know. I was just describing the general problem in order to explain why opening CSV files directly is not a good idea if your intent is for part of it not to be interpreted by Excel.

But perhaps there are problems that you had not anticipated.

For example, consider 123000E123 and 123E123. Excel interprets those as 1.23E+128 and 1.23E+125 respectively. Given those numeric values, I don't believe we can know that they were 123000E123 and 123E123 originally.

You're right. In notepad it displays properly. When I open it in excel it doesn't. How should I fix that given that I do not have control over which program is used to open the file on the other company's end that opens my csv?

IMHO, you cannot fix it if you cannot expect your end-user to use Excel, much less import the CSV file and make the appropriate column format changes that I described.

Perhaps you're making a mountain out of a molehill. If the end-user might not use Excel, they might not see any problem, in the first place. Other applications might not convert the data the same way.

Some speculative ideas -- probably misdirections (sorry)....

If the end-user does not use Excel (or another MSFT product), you might want to write your own VBA procedure to save the CSV file, ensuring that there are double-quotes around all text data.

(You should also double any double-quotes that are part of the data. Refer to RFC 4180, which post-dates the MSFT implementation and which is not yet a standard anyway, AFAIK.)

Even though Excel ignores the surrounding double-quotes, other applications might know to treat the data as text.

Alternatively, you might look into saving as XML. I have no experience with it. But I believe that does preserve formatting. However, I suspect that fewer applications can open XML files.

Good luck!

 

[joeu2004]

For example, consider 123000E123 and 123E123. Excel interprets those as 1.23E+128 and 1.23E+125 respectively. Given those numeric values, I don't believe we can know that they were 123000E123 and 123E123 originally.

.... Unless you have a priori knowledge of the number of digits that precede the "E"?

If you know, describe the syntax of all text that Excel might interpret as a number, and I'm sure that someone will be able to provide a valild conversion.

Unfortunately, I'll be traveling soon. Lots to think about. So I should not participate further.

 

[gcefaloni]

For your example, I know. I was just describing the general problem in order to explain why opening CSV files directly is not a good idea if your intent is for part of it not to be interpreted by Excel.

But perhaps there are problems that you had not anticipated.

For example, consider 123000E123 and 123E123. Excel interprets those as 1.23E+128 and 1.23E+125 respectively. Given those numeric values, I don't believe we can know that they were 123000E123 and 123E123 originally.




IMHO, you cannot fix it if you cannot expect your end-user to use Excel, much less import the CSV file and make the appropriate column format changes that I described.

Perhaps you're making a mountain out of a molehill. If the end-user might not use Excel, they might not see any problem, in the first place. Other applications might not convert the data the same way.

Some speculative ideas -- probably misdirections (sorry)....

If the end-user does not use Excel (or another MSFT product), you might want to write your own VBA procedure to save the CSV file, ensuring that there are double-quotes around all text data.

(You should also double any double-quotes that are part of the data. Refer to RFC 4180, which post-dates the MSFT implementation and which is not yet a standard anyway, AFAIK.)

Even though Excel ignores the surrounding double-quotes, other applications might know to treat the data as text.

Alternatively, you might look into saving as XML. I have no experience with it. But I believe that does preserve formatting. However, I suspect that fewer applications can open XML files.

Good luck!

I'll reach out to them and see if they can do something on their end to fix the issue they are having. Thanks a ton for your input, even if it might not have provided a solution, it surely helped me understand the problem a lot better and save me tons of time in trying to figure out something that would lead me into a dead-end anyways. Very appreciated.