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

 

[ISY]

Hi

Formula:
=TEXT(A1,"0,00000E+00")

 

[gcefaloni]

Hi ISY,

That does exactly what I do NOT want it to do. I do NOT want it to convert to 1.35087E+12. Excel is converting it to that automatically. I need to to stay as 135087E67. I tried "TEXT(A1,"0") but it gives me tons of 0s after the string. I also tried TEXT(A1,"TEXT") but it gives me a "#VALUES" error.

Thanks for again for your continued help!

 

[popipipo]

Try this input:
'135087E67

 

[Rick Rothstein]

There has got to be a better formula than this, but this one works...

=SUBSTITUTE(LEFT(TEXT(A1,"0.###############E+00"),FIND("E",TEXT(A1,"0.###############E+00"))),".","")&TEXT(MID(A1,FIND("+",A1)+1,3)-LEN(SUBSTITUTE(LEFT(TEXT(A1,"0.###############E+00"),FIND("E",TEXT(A1,"0.###############E+00"))),".",""))+2,0)

However, if the value you are trying to put in the cell is 135087E67, can't you just format the cell as Text before pasting or typing the value into the Formula Bar?

 

[gcefaloni]

Thank you for your reply but while your formula works for this specific instance, it doesn't work at all for other strings in the dataset (obviously because this addressed specifically scientific notation). The format as text option doesn't seem to work because I am exporting this sheet as a csv and it is when I convert to .csv that it doesn't keep the format. I've tried to pastespecial formats prior to pasting values in VBA section, tried changing all cells as text beforeall. Nothing seems to work so far.

There has got to be a better formula than this, but this one works...

=SUBSTITUTE(LEFT(TEXT(A1,"0.###############E+00"),FIND("E",TEXT(A1,"0.###############E+00"))),".","")&TEXT(MID(A1,FIND("+",A1)+1,3)-LEN(SUBSTITUTE(LEFT(TEXT(A1,"0.###############E+00"),FIND("E",TEXT(A1,"0.###############E+00"))),".",""))+2,0)

However, if the value you are trying to put in the cell is 135087E67, can't you just format the cell as Text before pasting or typing the value into the Formula Bar?

 

[joeu2004]

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.

If all the data that should be text are the entire content of their respective columns, it is better to import the CSV file instead of opening it directly in Excel.

Otherwise, if any of your IDs have more than 15 digits before the "E", there is a risk of corrupting the data. For example, if the data is 1234567890123456E123, Excel interprets it as the number 1.23456789012345E+138. We lose the "6" before the "E".

If you can import the CSV file (press Data > From Text; it is not necessary to change ".csv" to ".txt"), click Next to select the delimiter (comma), then click Next again. In the final dialog box, select each column that should be text, and click next to Text.

 

[joeu2004]

I am exporting this sheet as a csv and it is when I convert to .csv that it doesn't keep the format

That is incorrect. As long as the data looks like 135087E67 in the cell when you save as CSV, Excel writes 135087E67 into the file. Open the file with Notepad to confirm.

The problem is: a CSV file is a text file with no formatting information. When we open the file in Excel, Excel interprets the data the same as if we typed it manually into a cell whose format is General.

Consequently, 135087E67 is the number 135087E+67, which Excel displays as 1.35E+72 by default. Even "135087E67" is interpreted as a number; Excel ignores the double-quotes that completely surrounds the data for a cell.

 

[gcefaloni]

The problem I'm getting isn't so much about truncating values and losing decimals. It's the fact that it displays as scientific notation once converted to csv and that the system that reads that information (in a third party company over which I have no control on) cannot read it if it's not displayed properly as text. So I really need it to remain as text rather than be converted as sci. notation.

If all the data that should be text are the entire content of their respective columns, it is better to import the CSV file instead of opening it directly in Excel.

Otherwise, if any of your IDs have more than 15 digits before the "E", there is a risk of corrupting the data. For example, if the data is 1234567890123456E123, Excel interprets it as the number 1.23456789012345E+138. We lose the "6" before the "E".

If you can import the CSV file (press Data > From Text; it is not necessary to change ".csv" to ".txt"), click Next to select the delimiter (comma), then click Next again. In the final dialog box, select each column that should be text, and click next to Text.

 

[gcefaloni]

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?

That is incorrect. As long as the data looks like 135087E67 in the cell when you save as CSV, Excel writes 135087E67 into the file. Open the file with Notepad to confirm.

The problem is: a CSV file is a text file with no formatting information. When we open the file in Excel, Excel interprets the data the same as if we typed it manually into a cell whose format is General.

Consequently, 135087E67 is the number 135087E+67, which Excel displays as 1.35E+72 by default. Even "135087E67" is interpreted as a number; Excel ignores the double-quotes that completely surrounds the data for a cell.