The Logicbroker Blog

Excel Scientific Notation: Disable/Prevent

Apr 19, 2018 12:32:56 PM / by Jeff Keltz

Have you ever tried putting a long number (i.e. product identifier) in an Excel sheet, only to have Excel say “I don’t like your value, I am going to put it into scientific notation”? I’ve been there as well, and it may just be one of the most frustrating things you can encounter.

 

It is especially exasperating when trying to update a CSV inventory feed to send to a supplier, a common process within the Logicbroker portal. You create the spreadsheet, save it as a CSV, reopen it, and then BOOM - all of your data is in scientific notation again!

 

Although there is no definitive fix (trust me, I’ve looked up and down the internet, and in 2018, this issue still isn’t resolved in MSE), there are a few simple workarounds that will have you updating inventory and creating accurate CSV files in no time.

 

Workaround 1: Appending a ‘before the field’ contents

Step 1:

Open a blank workbook in excel.

Step 2:

Input your data.

Excel-Overides-Input-1

At this point, you will notice Excel overrides your input, and changes your numbers to scientific notation.

Have no fear, because at this point excel does still retain your original data.

Step 3:

Highlight a single cell and in the field modifier box, add an “ ‘ “ (apostrophe) before the number.

Add-Apostrophe-Gif

This tells Excel that the data after the apostrophe is not a number, but text, excel then does NOT convert this value to scientific notation. This is noted by Excel putting a green arrow in the top left of the cell.

Step 4: Save your workbook as an Excel type to begin with. This allows you to have a template to work with in the future.

 step 4

 

Step 5:

When you need to export/save this file as a CSV, open your Excel version of the workbook. Make any changes, and then go to file >Save As. Now you will save your file as a CSV.

Be careful not to open and save this CSV file in Excel, as you will run the risk of the scientific notation ruining your data. If you need to make any changes to the CSV, open the original Excel file, and use that as a template. Save as a CSV when needed.

step 5

Workaround 2: Formatting before you add data

Step 1:

Add column headers as needed. Before adding any data, Go to the formatting box, click the dropdown, and go to: “More Number Formats”

 step 1

Step 2:

From the format cells dialogue, go to “Custom”. Click on the zero (“0”).

 step 2

Step 3:

If your product identifier is the same length for every value, enter zeroes the same length as your value’s length. Click “OK”

 step 3

Excel is now ready for the data. When you input your values, it will not transform them into scientific notation.

Step 4: Input your data

Step 5: Save your workbook as an Excel type to begin with. This will allow you to have a template to work with in the future.

 

 step 5 part 2

 

Step 6:

When you need to export/save this file as a CSV, open your Excel version of the workbook. Make any changes, and then go to file >Save As. Now you will save your file as a CSV.

 

 

step 6

 

Be careful not to open and save this CSV file in Excel, as you will run the risk of the scientific notation ruining your data. If you need to make any changes to the CSV, open the original Excel file, and use that as a template. Save as a CSV when needed.

Important steps to remember:

Once you have your excel file saved, you will want to use that as your template. Any time you need a new CSV file, open the Excel template, make adjustments, and then save a new version as the CSV.

Unfortunately there is no definitive way to prevent excel from turning your values into scientific notation, but these are two of the best workarounds to make sure your product data does not get corrupted.

Topics: Blog

Jeff Keltz

Written by Jeff Keltz

Born and raised in Connecticut, but now living in the Washington DC area, Jeff Keltz is an expert regarding data-driven deicisons. His passion for analytics is evident in the way he approaches problems, and how he visualizes solutions. Jeff’s diverse knowledge and understanding includes Computer Programming, Databases, Data Visualization, and Client Delivery. He is a technology-driven people-person, who loves to see brands succeed. Jeff’s expertise is using available technology to be flexible and efficient. Recently, he has helped large and small brands alike go from eCommerce “newbies”, to technology connoisseurs. Always leveraging data in his decisions, Jeff finds ways to assist brands increase their success, understanding, and positive reputation within the market. “Never forget the ‘Three T’s’: Technology, Teamwork, and Tacos” – Jeff Keltz

      Contact Us

      Subscribe to Email Updates

      Recent Posts