How to Prevent Excel from Truncating Long Numbers Exported from Acrobat
Question details
Users need a reliable method to stop spreadsheet software from rounding or truncating 22-digit identifiers when exporting data from Adobe Acrobat PDF files.
- Product
- Microsoft Excel, Adobe Acrobat DC
- Device & OS
- not provided
- Scenario
- Exporting tabular data containing very long identification numbers from a PDF file into a spreadsheet.
- Observed behavior
- The spreadsheet software treats the 22-digit identifiers as numeric values, automatically rounding and truncating them to 15 significant digits, resulting in permanent data loss.
Verify that the 22-digit identifiers are fully intact in the original PDF file, and consider exporting the PDF to a plain text file before importing it into your spreadsheet to maintain data integrity.
Import Data as Text Using the Data Import Wizard
Bypass direct PDF-to-Excel export limitations by saving the file as Text first, then explicitly defining the identifier column as Text during the import process.
Spreadsheet applications adhere to the IEEE 754 standard, which only allows 15 significant digits of precision for numeric values. Any number longer than this will have its remaining digits converted to zeros. To prevent this, you must instruct the software to read the numbers as text strings before the data is loaded.
In Adobe Acrobat DC, go to File > Export To > Spreadsheet > XML Spreadsheet or Plain Text, and save the file to your computer.
Open a blank workbook in Excel, navigate to the Data tab, and select 'From Text/CSV'.
Select your exported file and click Import. When the preview window appears, click the 'Transform Data' button.
Locate the column containing your 22-digit numbers. Click the data type icon in the column header (usually displaying '123') and change it to 'Text'.
Click 'Close & Load' to bring the data into your spreadsheet with all digits perfectly intact.
Use Text to Columns for Misaligned PDF Exports
If Adobe Acrobat exports all of your PDF data merged into a single Excel column, use the Text to Columns feature to separate the data and protect the long numbers.
Pre-format Destination Cells as Text Before Pasting
If you are manually copying and pasting data directly from a PDF viewer into your spreadsheet, format the destination cells as Text beforehand.
Import and Format PDF Data Accurately in WPS Spreadsheet
WPS Office offers a highly intuitive Data Import Wizard that easily handles large datasets and long strings. You can effortlessly define data types during the import process to ensure 22-digit numbers are never truncated.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a blank spreadsheet.
- 2. Start Import: Navigate to the Data tab on the top ribbon and select 'Import Data'.
- 3. Select Your File: Choose the plain text file you exported from Adobe Acrobat and select 'Delimited' as the file type.
- 4. Format as Text: In the Data Preview step, highlight the column containing your long identifiers and set the Column Data Format to 'Text'.
- 5. Complete Import: Click Finish. Your 22-digit numbers will be successfully imported without any loss of precision.
Frequently Asked Questions
Why does Excel change my 22-digit number to end in zeros?
Spreadsheet programs like Excel use the IEEE 754 standard for floating-point arithmetic, which provides a maximum precision of 15 significant digits. Any digits beyond the 15th are automatically converted to zeros to prevent calculation errors. Converting the cell format to Text prevents the software from treating it as a mathematical number.
How do I fix numbers that display in scientific notation (e.g., 1.5E+14)?
Scientific notation appears when a number is too large to display in standard format, or if the column width is too narrow. To view the full string, simply widen the column, or select the cells, right-click, choose 'Format Cells', and change the format to 'Text' or 'Custom' with a '0' value.
Why did Acrobat export all my PDF data into a single column?
Depending on how the PDF was originally formatted or created, Acrobat may struggle to identify table structures during export, resulting in all text being grouped together. You can fix this easily by using the 'Text to Columns' feature located under the Data tab in your spreadsheet software.
