Hmobius Notes |||

Correcting ISBNs Converted to Scientific Notation in Excel

When Excel opens a CSV file or other text-based file where it has no formatting information for a column of data, Excel tries to predict the type of that data - sometimes with less than desirable results. A good case in point is that of ISBNs - 10 or 13 digit numbers like this 9780192760164. Excel interprets these as numbers and then decides to represent these in scientific notation like 9.7801E+12.

Fortunately, there is a quick way to correct Excel and make it treat ISBNs as text and not numbers.

  1. Select the column containing the mis-formatted ISBNs in your spreadsheet
  2. Click on the Data tab in your Excel Ribbon
  3. Click the Text to Columns button. If you’ve ever imported data from a text file in to Excel, this wizard will look very familiar.
  4. Click Next twice. On the third screen of the wizard, choose Text to be the Column data format.
  5. Click Finish.
Up next Changing the Delimiter of a CSV file to a Pipe or Other Character Comma-Separated Files are incredibly useful, but a lot of the frustration with them from my point of view is that they use commas to separate values Jeff Beck - Truth (1968)
Latest posts Bruce Dickinson - Skunkworks (1995) Eddie Izzard - Definite Article (1994) AC\DC - High Voltage (1976) Jimi Hendrix - Songs for Groovy Children (2019) Animals As Leaders - The Joy of Motion (2014) Converge - Jane Doe (2001) Steven Price - Gravity OST (2013) Robin WIlliams - Live At The Met (1986) Deafheaven - New Bermuda (2015) Down - NOLA (1995) Pink Floyd - Dark Side of the Moon (1973) Frank Zappa - Hot Rats (1969) Jeff Beck - Truth (1968) Correcting ISBNs Converted to Scientific Notation in Excel Changing the Delimiter of a CSV file to a Pipe or Other Character Standard Grixis Dredge in ELD Handy LINQ stuff Visual Studio Code Setup C# Coding Standards, Mid 2019 Edition Inheritance Tracks Simic Flash in M20