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.
- Select the column containing the mis-formatted ISBNs in your spreadsheet
- Click on the Data tab in your Excel Ribbon
- 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.
- Click Next twice. On the third screen of the wizard, choose Text to be the Column data format.
- Click Finish.