After Download Data and inserting values for Product code, Product name, Exp Date, Bin Loc and Physical QOH, the column for Product Code, which is listed as, for example, 191 on my excel spreadsheet, comes over as an error and is displayed as though I loaded 191.0. and says product ‘191.0’ does not exist. Same error for rest of the SKUs
How can I fix the problem.
Excel (and Microsoft software in general) is somewhat opinionated about what it should do under certain circumstances.
In this case, Excel treats any string that might look like a number as a number (i.e. 123 turns into 123.0). Sometimes longer strings are treated like scientific numbers (1234567890000 turns into 1.2E12) and numbers prepended with 0s (000123 turns into 123 or 123.0) have those 0s truncated.
It’s all very frustrating.
Microsoft support suggests formatting the cells as text.
However, I don’t know if formatting the number as text has ever worked for me. So we usually recommend handling these kinds of issues by adding a single quote in front of the number to instruct Excel to treat the value as a string (as suggested in this answer Why does Excel treat long numeric strings as scientific notation even after changing cell format to text - Super User).
If there are hundreds or thousands of cells that need to be “processed” in this way then we jump through some hoops to convert the cells to text. The “Superuser” article I linked to above has the following comment buried under one of the answers.
You can make an extra column and put in the formula =TEXT(A1,0) where A1 is the cell that has the misbehaving data. Then you can copy and right-click/paste special/values to actually change it to “number stored as text.”
This is the approach I generally use and recommend but you can try the suggestion from Microsoft first to see if that’s easier.
Justin
An additional strategy that I always use is the text to columns feature. Highlight the entire column that has the issue, then go to the data tab and choose text to columns
This will force all of the cells in the column into a text format, which will eliminate the .0 issue. The only time this doesn’t work is if the data comes out of OpenBoxes with the .0 already there. In that case the formula Justin suggested would probably work. But we are trying to fix all of the places where data exports incorrectly to excel, so if you are seeing that in an export please share the url and export so we can add it to our fix list.
