Common Excel problems
Overview
There are often issues with how Microsoft Excel handles numbers. There are some solutions you can use to manage:
Numbers formatted as '9.7686E+21'
Microsoft Excel often abbreviates long numbers using "Floating-point arithmetic". It will turn a long number such as a barcode into a shorter number, like this '9.7686E+21'. While useful for mathematicians, it's less so for presenting long strings of numbers such as barcodes or phone numbers.
This however, is easy to resolve.
-
Highlight the entire Excel column by clicking on the column heading.
-
Change the Column format to "Number".
-
Reduce the number of decimal places to 0.
Leading 0s removed from codes or phone numbers
When Microsoft Excel considers a column to be formatted as a number, it will remove any leading 0s. For example, "09" becomes "9". This is sometimes unhelpful if you wish to store telephone numbers or barcodes in your document.
This can be solved by formatting the column as Text.
If using a .csv file, if you open the file directly from Excel, you may find that the leading zeros have been stripped from your numbers before you’ve had a chance to format the columns.
This can be solved by importing the data into Excel instead of opening it directly.
-
Open a blank document, select the Data tab, and then click From Text to initiate the From Text wizard.
-
In Step 3 of the Text Import Wizard, you will be given an opportunity to format specific columns as "Text" before they are created in the document to allow leading 0s to be retained.
While sometimes unavoidable, it is recommended to not use codes or barcodes that begin with "0"s. It will save you the hassle of having to update the column format whenever you use Microsoft Excel.