About Links Archives Search Feed Albums of Note

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.
Posted on October 24, 2019   #Code  

← Next post    ·    Previous post →