How to convert 5.00E+13 to number in cells

[BlackPanther]

I have a column with the value " 50000000000000 " in each cell


But it shows the following :

5.00E+13

5.00E+13

5.00E+13

5.00E+13

5.00E+13

5.00E+13

<tbody>
</tbody>

How can i convert it to " 50000000000000"

This has happened when stored in .csv file.

Thanks in advance

 

[Rick Rothstein]

Try formatting the cell as Number and set the decimal places to zero.

 

[BlackPanther]

Try formatting the cell as Number and set the decimal places to zero.

@
Rick Rothstein

I have tried formatting, but it doesn't get saved. I tried using - Format - Decimal - 0 . it changes to 50000000000000 but after i save - close - reopen . it goes back to 5.00E+13.

 

[Michael M]

Try making the columns wider, to suit the actual number required !

 

[joeu2004]

I have a column with the value " 50000000000000 " in each cell[.] But it shows the following : 5.00E+13
[....]
How can i convert it to " 50000000000000"[.] This has happened when stored in .csv file.

I have tried formatting, but it doesn't get saved. I tried using - Format - Decimal - 0 . it changes to 50000000000000 but after i save - close - reopen . it goes back to 5.00E+13.

This is a misunderstanding. If the cell displays 50000000000000, it is indeed written to the CSV file as 50000000000000. Open the file in Notepad to confirm.

The problem is: whenever you open the CSV file in Excel, Excel will reinterpret the data as if you typed it manually, and it sets the cell format to General.

When the cell format is General, Excel displays any number with more than 11 digits in Scientific form, regardless of the cell width.

You can work around that behavior by formatting the cells as Number with zero decimal places first, then use Data > Get External Data > From Text to import the CSV file.

However, that might be tedious to do, especially if your column data have mixed formats.

[EDIT] PS.... I suspect that "numbers" with that many digits are really identifiers which you do not intend to use in arithmetic; for example, account "numbers". In that case, the cells or column should be formatted as Text. You can do that directly in Get External Data > From Text instead of formatting the cells beforehand.

 

[BlackPanther]

This is a misunderstanding. If the cell displays 50000000000000, it is indeed written to the CSV file as 50000000000000. Open the file in Notepad to confirm.

The problem is: whenever you open the CSV file in Excel, Excel will reinterpret the data as if you typed it manually, and it sets the cell format to General.

When the cell format is General, Excel displays any number with more than 11 digits in Scientific form, regardless of the cell width.

You can work around that behavior by formatting the cells as Number with zero decimal places first, then use Data > Get External Data > From Text to import the CSV file.

However, that might be tedious to do, especially if your column data have mixed formats.

[EDIT] PS.... I suspect that "numbers" with that many digits are really identifiers which you do not intend to use in arithmetic; for example, account "numbers". In that case, the cells or column should be formatted as Text. You can do that directly in Get External Data > From Text instead of formatting the cells beforehand.

Tried all the above things but still didnt work

 

[BlackPanther]

7.20576E+16

7.20576E+16

7.20576E+16

7.20576E+16

7.20576E+16

7.20576E+16

7.20576E+16

7.20576E+16

<colgroup><col></colgroup><tbody>
</tbody>

This is what it shows in the column , the file itself is in CSV format , i just need it to expand

 

[joeu2004]

7.20576E+16

7.20576E+16

<TBODY>
</TBODY>

[....]
This is what it shows in the column , the file itself is in CSV format , i just need it to expand

I'm sorry, but that is not very clear.

First, open the CSV file in Notepad. Do the numbers look like you require, for example 1234567890123456? Or do they look like 1.23456E+16 or 12345600000000000 (11 zeros) or 1234567890123450 (16th digit is always 0)?

If the latter, your data is already corrupted, and there is nothing you can do to correct it. You must start over with the original data.

Second, what is this data? Are they truly numbers that you might use in computation? Or are they really identifiers that we call "card number", "product number", "employee number" etc because they look like numbers?

If the latter (identifiers), the data should be entered as text to begin with because Excel has arbitrary data entry and formatting limitations that affect true numbers with more than 15 significant digits, like 7.20576E+16.

Do one of the following to enter the data as text: (a) format the cell as Text beforehand; or (b) enter '1234567890123456, with a leading single-quote, aka an apostrophe; or (c) enter the formula ="1234567890123456".

But even if the data is entered as text initially, limitation #3 below still applies when a CSV file is opened in Excel.

-----

If the data are true numbers that you might use in computation, you are playing with fire when you have such large numbers. Bear in mind the following limitations:

1. The computer can only represent 16-digit integers up to 9,007,199,254,740,992 exactly, due to the way that Excel stores numbers (64-bit binary floating-point).

2. Excel formats only the first 15 significant digits, rounding the 16th digit. So if the result of a calculation is 1,234,567,890,123,456, it appears as 1,234,567,890,123,460 when formatted as Number, even though it is truly stored as 1,234,567,890,123,456.

And the value saved to a CSV file will be 1,234,567,890,123,460 because it is saved as it appears in the cell.

This limitation is only for numeric values, not text. So if 1234567890123456 is text (ISTEXT returns TRUE), then 1234567890123456 is saved to a CSV file, again because it is save as it appears in the cell.

3. When entering data, Excel interprets only the first 15 significant digits, replacing any digits on the right with zero. So if you type 1,234,567,890,123,456, the numeric value will be exactly 1,234,567,890,123,450, not 1,234,567,890,123,456.

This limitation applies when you open a CSV file, even if the CSV data looks like "1,234,567,890,123,456" or "1234567890123456". Excel removes the double-quotes and interprets the string as the number 1,234,567,890,123,450.

This limitation does not apply when you import a CSV file and select Text for the column format in import Step 3. Excel removes the double-quotes and interprets the string as the text 1,234,567,890,123,456 or 1234567890123456.

But that presumes the data looks like 1234567890123456, "1,234,567,890,123,456" or "1234567890123456" when you open the CSV file in Notepad.

-----

If you need further assistance, I suggest that you upload example Excel and CSV files to a file-sharing website and post the share/public URL in a response here.

The Excel file should contain the original data before you save to a CSV file, not data that you opened as or imported from a CSV file.

The CSV file should contain the data saved as CSV from Excel with the original data.

 

[BlackPanther]

I'm sorry, but that is not very clear.

First, open the CSV file in Notepad. Do the numbers look like you require, for example 1234567890123456? Or do they look like 1.23456E+16 or 12345600000000000 (11 zeros) or 1234567890123450 (16th digit is always 0)?

If the latter, your data is already corrupted, and there is nothing you can do to correct it. You must start over with the original data.

Second, what is this data? Are they truly numbers that you might use in computation? Or are they really identifiers that we call "card number", "product number", "employee number" etc because they look like numbers?

If the latter (identifiers), the data should be entered as text to begin with because Excel has arbitrary data entry and formatting limitations that affect true numbers with more than 15 significant digits, like 7.20576E+16.

Do one of the following to enter the data as text: (a) format the cell as Text beforehand; or (b) enter '1234567890123456, with a leading single-quote, aka an apostrophe; or (c) enter the formula ="1234567890123456".

But even if the data is entered as text initially, limitation #3 below still applies when a CSV file is opened in Excel.

-----

If the data are true numbers that you might use in computation, you are playing with fire when you have such large numbers. Bear in mind the following limitations:

1. The computer can only represent 16-digit integers up to 9,007,199,254,740,992 exactly, due to the way that Excel stores numbers (64-bit binary floating-point).

2. Excel formats only the first 15 significant digits, rounding the 16th digit. So if the result of a calculation is 1,234,567,890,123,456, it appears as 1,234,567,890,123,460 when formatted as Number, even though it is truly stored as 1,234,567,890,123,456.

And the value saved to a CSV file will be 1,234,567,890,123,460 because it is saved as it appears in the cell.

This limitation is only for numeric values, not text. So if 1234567890123456 is text (ISTEXT returns TRUE), then 1234567890123456 is saved to a CSV file, again because it is save as it appears in the cell.

3. When entering data, Excel interprets only the first 15 significant digits, replacing any digits on the right with zero. So if you type 1,234,567,890,123,456, the numeric value will be exactly 1,234,567,890,123,450, not 1,234,567,890,123,456.

This limitation applies when you open a CSV file, even if the CSV data looks like "1,234,567,890,123,456" or "1234567890123456". Excel removes the double-quotes and interprets the string as the number 1,234,567,890,123,450.

This limitation does not apply when you import a CSV file and select Text for the column format in import Step 3. Excel removes the double-quotes and interprets the string as the text 1,234,567,890,123,456 or 1234567890123456.

But that presumes the data looks like 1234567890123456, "1,234,567,890,123,456" or "1234567890123456" when you open the CSV file in Notepad.

-----

If you need further assistance, I suggest that you upload example Excel and CSV files to a file-sharing website and post the share/public URL in a response here.

The Excel file should contain the original data before you save to a CSV file, not data that you opened as or imported from a CSV file.

The CSV file should contain the data saved as CSV from Excel with the original data.

It shows clearly in text file - 72057598332895322

The value is kinda product ID , i need it for computation too . tried everything . I tried uploading it as a text file by going to data - > Get external dATA - > from text , delimited it with comma (as the file contains it) and i get the result too - 72057598332895322 , but when i try to save i get the following :

" BSFG.csv may contain features that are not compatible with CSV ( comma delimited ). Do you want to keep the workbook in this format?"

I hit yes thats the only option i get . but when i reopen the file its gone.

 

[joeu2004]

It shows clearly in text file - 72057598332895322
The value is kinda product ID , i need it for computation too

What kind of "computation" do you do with a product ID?(!)

If you only mean comparison, the product ID can be imported as text.

Otherwise, please provide an example of the computation. Because if you mean arithmetic computation, you have a problem that cannot be avoided.

As a number, 72057598332895322 exceeds the limitations of Excel arithmetic (really the computer's 64-bit binary floating-point arithmetic).

I tried uploading it as a text file by going to data - > Get external dATA - > from text , delimited it with comma (as the file contains it) and i get the result too - 72057598332895322

Only if you also select Text for the column format in step #3 of the import wizard.

Otherwise, initially it appears as 7.20576E+16 because the cell format is General. You can change the cell format to Number with zero decimal places.

Then the result appears to be 72057598332895300 because Excel truncates data entry after the 15th significant digit, replacing the remaining digits with zero. (And because formats only up to 15 significant digits.)

But despite appearances, the actual value is 72057598332895296.

You might write =72057598332895300+22, which results in 72057598332895312. But a closer approximation would be =72057598332895300+24, which results in 72057598332895328.

when i try to save i get the following :
" BSFG.csv may contain features that are not compatible with CSV ( comma delimited ). Do you want to keep the workbook in this format?"

I get that warning, too; usually, it is specious. I just ignore it (press "yes", as you did), with no dire consequences unless there truly is an incompatibility.

I hit yes thats the only option i get . but when i reopen the file its gone.

What is gone: the file or the cell value?!

Neither should be the case.

However, note that when you save as CSV, Excel saves only the currently active worksheet.

If that is not the worksheet into which you imported the CSV file, that would explain the compatibility warning as well as whatever is "gone".