• Contents

Solving some common Excel problems

Last updated 05/10/2017

We find customers run into some common Microsoft Excel problems - don't worry, they are easily solved.

Numbers formatted as '9.7686E+21'

Microsoft Excel often abbreviates long numbers using something called Floating-point arithmetic - it will turn a long number such as a barcode into a shorter number, like this '9.7686E+21'.

Very useful for mathematicians but not much use for presenting long strings of numbers such as barcodes or phone numbers.

Luckily this is easy to resolve.

  1. Highlight the entire column by clicking on the column heading.
  2. Change the Column format to Number.
  3. Reduce the number of decimal places to 0.

Leading 0s removed from codes or phone numbers

When excel considers a column to be formatted as a number, it will remove any leading 0s. So for instance ’09' will just become ‘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.

  1. Open a blank document then select the Data tab from the ribbon. Next click ‘From Text’, and follow the wizard.
  2. 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, which will retain the leading 0s.

Sometimes it is unavoidable, but we’d always recommend not using codes or barcodes that begin with 0s - it will save you the hassle of having to format the column correctly whenever you use Excel.