Knowledge Base

Problem Overview

When exporting spreadsheets from Alma, you may encounter formatting issues where MMS IDs and other long numeric identifiers display incorrectly. Common problems include:

• Scientific notation: Numbers like 991234567890123456 appear as 9.91235E+17
• Truncated numbers: Long IDs get cut off or rounded (e.g., 1234567890123456789 becomes 1234567890123450000)
• Leading zeros removed: 00123456789 becomes 123456789
• Incorrect data types: Text fields converted to numbers automatically

Why This Happens

Excel automatically detects data types when opening files, and treats long numeric strings as numbers rather than text. Since Excel's number precision is limited to 15 significant digits, longer numbers get converted to scientific notation or lose precision.

Prevention Methods (Best Practices)

This method will not recover digits that were already lost due to rounding or truncation. However, if the full value is still stored in the cell, it will correct the display by converting the number to text.

Method 1: Import Data Properly

Instead of double-clicking the file, use Excel's Data Import wizard:

1. Open Excel (blank workbook).
2. Start the import process.
   1. Windows users should go to Data > Get Data > From File > From Excel Workbook or Data > Get Data > From File > From Text/CSV, depending on the file type.
   2. Mac users should go to Data > Get Data (Power Query) > Excel workbook or Data > Get Data (Power Query) > Text/CVS, depending on the file type.
3. Upload your exported file.
4. Follow the import wizard.
5. Change the data type from "Automatic" or "Number" to "Text."
6. Load the file.

Power queries may convert your data into a table. To remove the table formatting, click on any table cell(s), then go to Table > Convert to Range.

Method 2: Format Columns Before Importing

1. Open a blank Excel workbook.
2. Select the column where MMS IDs will go (usually column A).
3. Right-click, then select Format Cells.
4. Choose "Text" format in the "Number" tab.
5. Now import or paste your data.

Method 3: Format Cells in Excel

If your MMS IDs are displaying in scientific notation but haven’t been saved yet, this method can force Excel to treat them as text:

1. Select the column with the affected values.
2. Go to the "Home" ribbon.
3. Go to Format > Format Cells OR right-click and select Format Cells.
4. Go to the "Numbers" tab.
5. Set "Text" as the category.
6. Click OK.

Fixing Already-Corrupted Data

If Scientific Notation Has Already Occurred

Unfortunately, once Excel converts to scientific notation, the original precision is lost and cannot be recovered. You'll need to:

1. Go back to Alma and re-export the data.
2. Use one of the prevention methods above.
3. Do not save the corrupted file, as this makes the data loss permanent.

Alternative Solutions

Use a Text Editor First

1. Open the exported file in Notepad or another text editor.
2. Add an apostrophe (') before each MMS ID: '991234567890123456.
3. Save the file.
4. Open in Excel—the apostrophe forces text formatting.

Add Leading Characters in Alma Export

If possible, modify your Alma export to include a prefix like "ID:" before each MMS ID, which forces Excel to treat it as text.

Use Google Sheets

Google Sheets handles long numbers better than Excel. You can:

1. Upload the file to Google Sheets.
2. Format as needed.
3. Download as Excel format.

Quick Reference Checklist

Before Opening Files:

• [ ] Use Data Import wizard instead of double-clicking
• [ ] Set MMS ID columns to "Text" format during import
• [ ] Consider opening in text editor first to add formatting cues

If Problems Occur:

• [ ] Do NOT save the file if data is corrupted
• [ ] Re-export from Alma using proper import methods
• [ ] Check that all ID columns are formatted as Text

Additional Tips

• Always keep a backup of your original export file.
• When sharing files with colleagues, include formatting instructions.
• Consider using leading zeros or prefixes in export templates when possible.
• Test imports with a small sample before processing large datasets.

Still Having Issues?

If these methods don't resolve your formatting problems:

• Check if your Alma export settings can be modified to include text indicators.
• Contact your system administrator about export formatting options.
• Consider using specialized data processing tools for large datasets.