Stop Excel from removing leading zeros

It is important to remember that the when preparing data to be imported it must match exactly what is in Acctivate.  This includes any leading zeros for Product ID’s, UPC’s, etc.  If you have ever tried to input numbers to an excel spreadsheet you most likely discovered that Excel re-formats your number to something else, by removing leading zeros, changing a fraction to a date, or changing a long string of numbers or decimals to to scientific notation.   This can be frustrating and prevent your records from importing correctly.  In order to stop Microsoft Excel from changing the formatting of your data simply choose one of the two methods below.

The Apostrophe Method

By placing an Apostrophe in the cell before the number you are telling Microsoft Excel to disregard the cell formatting, and display the number as it has been entered into the cell.  This method means that even if someone were to change the cell formatting back to General and tries to edit a cell it will continue to look the same  instead of being auto-formatted by Excel.

  • Type an Apostrophe in the Cell
  • Enter the number as you want it to display and press Enter
  • Now the numbers should display and import correctly
  • This will allow you use MATCH and VLOOKUP functions in Excel  (Apostrophe will be ignored)

Format as Text Method

The second most common method to simply format the entire worksheet or a group of cells as Text before you enter your data.  Formatting cells to display as text will make Excel display the data exactly as it has been entered. (Remember to format your cells Before you input your data)

  • Select the entire sheet or a group of cells
  • Next you can either ‘Right Click’ within the cells you have selected or select the cell format drop down from the Home tab.
  • Select Text as the format method for your cells
  • Input your data and it will display exactly as entered

If you have additional questions or need assistance please contact Acctivate Support.

Tagged with: , , ,
Posted in Getting Started, Import Data, Microsoft - Last modified on May 22, 2017Eric Cartrite
Still need help?
Search all content on this site, contact support at 817-870-1311, or create a support request.