OmniDocHow to fix exporting Excel numbers into text
After the PDF table is exported to Excel, the numbers become text and cannot be summed or sorted. This article determines the cause based on symptoms and explains how to troubleshoot and repair common problems such as scanned documents, cell formats, thousandths, and negative signs.
Key points of this article
- If the numbers are aligned to the left and there is a small green triangle in the upper left corner of the cell, it usually means that the numbers are saved as text.
- Scanned PDF tables rely on OCR recognition. The recognition results need to be checked item by item against the original page. Decimal points and negative signs are prone to errors.
- Even after changing the entire column to numerical format, it still cannot be calculated. It is probably caused by invisible characters such as trailing spaces and full-width symbols.
- Thousands, currency symbols and negative numbers in parentheses will cause the numbers to be treated as text. It is more reliable to use search and replace to clean up after exporting.
After many people export financial statements, experimental data or purchase lists from PDF to Excel, they find that the numbers are all arranged to the left, the sum is 0, and the ordering is not based on size. This type of problem is often regarded as "the format is not preserved", but its essence is a number format problem: the table structure may be complete, but the cells store text instead of numerical values that can be calculated.
This article is written for people who need to import PDF tables into Excel for calculation, verification or summary. After reading, you can first use two or three checks to determine which layer the problem lies in, and then use the corresponding method to restore the numbers to a computable format, and know how to deal with scanned and signed numbers.
The following method also applies to tables from other sources, not limited to OmniDoc export results. For parts involving OCR recognition, please be sure to check important data against the original text.
How to export Excel numbers to text? One sentence conclusion
First determine whether the PDF is a text version or a scanned copy, then change the numeric column to a numerical format, and finally clean up symbols and spaces and use sum verification; the recognition results of the scanned copy must be manually checked.
| your situation | Recommended practices | reason |
|---|---|---|
| Text version PDF, the entire column of numbers is on the left | Select the entire column, change it to numeric format, and reconfirm with the column sorting function if necessary. | The cell format is text, and the formula will not calculate it as a number. |
| Scanned PDF, numbers are misplaced or missing decimal points | First perform OCR recognition, and then correct it column by column based on the original page. | There are errors in OCR recognition, and errors will not be automatically exposed. |
| Numbers with thousandths, currency symbols, or brackets | Use search and replace to remove the sign and change the parentheses to negative signs. | Symbols are mixed in numbers, and the spreadsheet software cannot recognize them as numerical values. |
| Header repeats on every page | Delete duplicate header rows on intermediate pages after exporting | Repeated text can blend into data columns, interfering with summing and sorting. |
| Just show others the layout | Keep text format and do not perform numerical conversion | Text is suitable for display, not calculation |
First determine what kind of problem it is
Different symptoms have different causes. Make a judgment based on the table below, which can save you a lot of invalid operations.
| symptoms | Common causes | How to confirm |
|---|---|---|
| The numbers are aligned to the left, and there is a small green triangle in the upper left corner of the cell. | The cell is in text format | Select the cell to check whether the format is text; use the ISNUMBER function on a blank cell to test, and if it returns FALSE, it is text. |
| Summing the entire column results in 0 | Numbers are all text | When summing a column, if the result is 0 and the number is visible to the naked eye, it can basically be determined to be text. |
| Only individual numbers cannot be calculated | There is a space or invisible character at the end | Double-click the cell to enter the editing state, move the cursor to the end to see if there is a blank space; use the LEN function to compare the number of digits |
| The decimal point, minus sign, or digit number of digits is incorrect | Scans with OCR recognition error | Try selecting a passage of text in the PDF; if you can't select it, it becomes an image; Compare with the original page to check the differences |
| Numbers contain commas, currency symbols, or parentheses | Format symbols mixed into the data | Check the editing bar to see if commas, ¥, or paired parentheses appear |
| The original PDF table does not select Chinese text | A scanned copy or table is an image | Similarly, OCR must be done first; otherwise, the exported content cannot be used as reliable data |
Inspect and fix each item one by one
It is recommended to check in the following order: first rule out simple causes, then address complex identification issues.
- Confirm the PDF type: Select a number in the PDF. If you can select it, it's a text version; if not, it's usually a scanned copy.
- OCR for the scanned footage first: Perform OCR on the scanned page in the PDF editor of the OmniDoc desktop client, then export it.
- Export to Excel: Select Export as Excel in the PDF Editor to obtain the table file.
- Open the table, select the number column, format it to a numeric value, and check if there are still green triangles.
- Clean up characters: Use Find to replace the milleth comma and currency symbol, and replace any extra spaces with empty ones.
- Sum the columns once, cross-check with the total rows of the original PDF, and only continue using the list if the results match.
Cause 1: OCR recognition error of the scan
The digits exported from the scanned copy need to be manually verified. OCR may mistake "8" for "3," lose decimal point recognition, or recognize negative signs as horizontal lines. The solution is: first compare the original page column by column in the exported table, focusing on the amount, ratio, and total rows. If errors appear on the same page, it means the original image is not clear. You can try rescanning or adjusting the original image before re-identification.
Reason 2: The cell format is text
This is a common situation. After converting entire columns to text, even if the content is numeric, the formula will ignore them. When repairing, select the entire column, format it to a numeric value, then double-check whether the numbers are right-aligned. If the format is changed and the character still leans left, it means there are hidden characters inside the numbers, and you need to enter the third scenario.
Reason 3: Symbols or spaces mixed into numbers
Milleliths, currency symbols, and negative numbers in parentheses make numbers recognized as text. It is recommended to use search replacement for unified processing: remove the comma and currency symbol, and change the parenthesis digit in the form (123) to -123. For the last space, you can enter a space in the lookup box and replace it with empty. After processing, sum is used to verify whether the result matches the original table.
How to Prevent It from Happening Next Time
- Before exporting, check the PDF type; all scans must be processed with OCR first, and the original files are kept for reference.
- Set the numeric columns in the table software in advance as numeric values, and after importing the data, perform a summation test to detect problems early.
- Minimize the source of the millenthile and currency symbols in reports to reduce post-export cleanup work.
- Before merging multi-page tables, check whether each page headers are duplicated, and remove any extra header rows after exporting.
- Key data such as amounts and ratios are checked item by item against the original PDF. Both OCR and machine conversion results require manual confirmation and are not suitable for direct formal reporting.
❓ Frequently Asked Questions (FAQ)
1. Can the exported numbers be directly calculated using formulas?
Only when the format is numerical can the formula be correctly calculated. Numbers in text format can first be changed to numeric format, or reconfirmed using the column function, and then summed for verification.
2. Can a scanned PDF table be exported to Excel?
Yes, first perform OCR recognition on the scanned pages, then export them to Excel. After recognition, the numbers need to be checked item by item with the original page, especially for decimal points, minus signs, and similar numbers.
3. What should I do if the border and merged cells disappear after exporting?
Borders and styles can be reset in the spreadsheet software. Merging cells needs to be manually adjusted according to the original table, with the export usually focusing on data content and basic structure.
4. Why do only some numbers in the same column become text?
This is usually because individual cells have a final space, full-width symbol, or invisible characters. Check the edit bar content of each abnormal cell one by one; after cleaning, restore the value to the value.
📝 Summary
When PDF tables are exported to Excel and numbers turn to text, most cases are caused by cell formatting, symbols, or scanned recognition, not because the table is lost. First, determine the PDF type, then change to numerical formatting, clean up symbols, and finally sum and cross-check with the original table to restore the data to a computable state.
