Large Numbers

ehrimat

New Member
Joined
Feb 28, 2012
Messages
4
Hello Everyone,

I use a certain package tracking software that allows me to export the tracking information into CSV format. The software does not yield any other options, one button that says "CSV Export"

When I open the file in Excel, the numbers that are 15 digits are formatted with a scientific notation ex:4.34E+11
From reading some of the posts, I understand that Excel can only handle a certain amount of digits as number. Also, in order to view large number I have to format the cell into text.
After I open the CSV file and change the cell format into text, some of the digits remain abbreviated while some need to be double clicked in order for the entire number to show.
I tried to rename the file as .txt I also tried to import the file into Access but neither worked for me.

My question is; is there a way, method, code that can help me maintain the full number.

Thank you kindly,
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
Try selecting all cells with scientific notation and change the format to "number" (eg: click the comma (,) icon in the upper area of your spreadsheet)
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi
Welcome to the board

Have any of the numbers more than 15 digits?
 

ehrimat

New Member
Joined
Feb 28, 2012
Messages
4
Here is a USPS tracking # 9101150134811209669286 and the way it appears is 9.10115E+21


Kind Regards,
 

ehrimat

New Member
Joined
Feb 28, 2012
Messages
4

ADVERTISEMENT

Try selecting all cells with scientific notation and change the format to "number" (eg: click the comma (,) icon in the upper area of your spreadsheet)

Thank you for the reply,

If I did that and used the comma the result will be the following:


9,101,150,134,811,200,000,000.00

instead of

9101150134811209669286

Thank you,
 

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
Right click "Format Cells" and select "Number" and change decimals from 2 to 0 and uncheck the box next to "Use 1000 Separator(,)".

Unfortunately, anything beyond 15 positions is not coming thru as a number. The only way I know how to get a number to display more than 15 positions is to import it (or format it) as "Text.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Here is a USPS tracking # 9101150134811209669286 and the way it appears is 9.10115E+21

In this case, you have to import it as text.

I tried to rename the file as .txt I also tried to import the file into Access but neither worked for me.

... but that's the way to do it. Rename the file as .txt and open it in excel. You are taken to the text import wizard. In the last panel, when you see the columns with a preview of the data, click on the header of the column with those numbers (the column is selected) and check the Text option button. This will import the numbers as text and you won't lose digits.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top