Physical Inventory – Change spreadsheet format from scientific notation to text

When creating a spreadsheet for import into POSIM, it’s recommended to format all cells as text (/knowledgebase/posim-physical-inventory/) to prevent numeric values from defaulting to scientific notation. For an already-existing spreadsheet that has not been preformatted as text, specific steps are often necessary to convert the cells back to their original format to ensure they import correctly into POSIM. This article outlines the steps to revert numeric cells that have been automatically converted to scientific notation back to text.

When making a Spreadsheet for use as an import into POSIM, it’s possible for the SKUs or UPCs to be converted to scientific notation by the spreadsheet program, which will prevent the spreadsheet from importing correctly.

Left: SKUs incorrectly formatted to scientific notation.
Right. SKUs correctly formatted as text.

1. Highlight the column(s) showing the numbers in scientific notation by clicking the column header. Hold down the shift key and click to select multiple columns at once.
2. Right-click inside the column, and select the Format Cells option from the menu that appears.
3. In the Format Cells menu under the Number tab, set the format as Number with Decimal places set to 0. This should cause the incorrectly formatted numbers to display in their entirety.
   
   Select Number, and set decimal places to zero to undo the scientific format.
   
4. Next, pick the Save As option under File, name the document, and save the file type as Text (Tab delimited).
   
   Save the corrected spreadsheet as Text (tab delimited).
   
5. Now create a brand new spreadsheet, select all cells, formatting them as text. See /knowledgebase/posim-physical-inventory/ for detailed instructions on formatting a spreadsheet as text.
6. In a text editor like Notepad, open the text (tab delimited) document from Step 4, highlight all information, right-click, and select Copy.
   
   Highlight and copy all information from the text (tab delimited) file in Notepad.
   
7. Select all cells and paste the copied text document into the newly formatted spreadsheet from Step 5 and save that spreadsheet as a .xlsx document.
   
   The correctly formatted spreadsheet is now ready to import into POSIM.