OmniDocWhen converting PDF to Excel, garbled characters and missing numbers appear, how to troubleshoot them one by one?
When garbled characters or missing numbers appear after converting PDF to Excel, first determine whether it is a text version or a scan, and then check the font, page range, number format, and total row in sequence. The article provides confirmation methods and repair methods for each step.
Key points of this article
- First drag and select the text in the PDF. If the text cannot be selected or the copy is blank, it means it is a scanned document and should be OCRed first and then converted.
- Most of the garbled characters come from font or encoding problems. Changing a conversion method or doing OCR first is more effective than repeatedly adjusting Excel.
- Number loss is common in text boxes, image tables and page ranges. Checking page by page is faster than retransmitting the whole.
- When the negative signs, parentheses, and thousandths are not written uniformly, the numbers will be saved as text, causing the summation result to be zero.
- Use total rows and sampling to compare and check after conversion. The sooner problems are discovered, the less rework will be done.
The original purpose of converting the table in PDF to Excel was to save time on manual entry. However, when I opened it, the Chinese text turned into question marks or squares, the amount was missing a few digits, and even the entire column of numbers disappeared. This type of problem is very common in financial statements, experimental data, purchase lists, and performance reports. Many people subconsciously try another conversion tool and try again, but the result is often the same.
This article is written for readers who have already transferred once, saw garbled characters or missing numbers, and want to find the reason before repairing it. We will not generally say "choose a good tool", but give a judgment method in order: first distinguish which category the problem belongs to, and then investigate each item one by one from easy to difficult. After reading, you should be able to judge how to process your own PDF, and also know how to check whether the data is complete after conversion.
If the output is garbled or missing, do you need to do OCR first? One sentence conclusion
The conclusion is: first determine whether the PDF is a scan. The scanned document does not have a text layer that can be extracted, and direct conversion will inevitably result in garbled characters or blanks, so you should perform OCR first. If the text version of the PDF appears garbled, it is most likely a font or encoding problem. You can check it one by one as follows.
| your situation | Recommended practices | reason |
|---|---|---|
| The text cannot be selected by dragging, or the copied text is blank. | OCR recognition first, then export to Excel | Scanned documents are essentially pictures and there is no text layer to extract. |
| Text can be selected, but it turns out to be a box or question mark | Check font embedding, change the conversion method and try again | Missing fonts or custom encoding causing character mapping errors |
| I can select text, but only part of the number is missing. | Check page ranges and text boxes, image tables | Some content may not be in the normal text layer |
| The numbers are all there, but the summation result is wrong. | Check if the number is saved as text | Inconsistent writing of negative signs, brackets, and thousandths will make Excel unable to calculate |
First determine what kind of problem it is
The causes of different symptoms vary greatly. Knowing the correct symptoms first can save you a lot of detours.
| symptoms | Common causes | How to confirm |
|---|---|---|
| The entire page is blank or garbled | Scan, or PDF without text layer | Drag and select text in the PDF to see if it can be highlighted and readable content can be copied. |
| Chinese characters become squares and question marks | The font is not embedded, or a custom font encoding is used | Use a PDF reader to copy a few paragraphs of text directly to Notepad to see if they are also garbled. |
| Some numbers are missing | Numbers are in text boxes, pictures, or obscured areas | Check the original page by page to see if the missing numbers are outside the table lines or in the pictures |
| Numbers become text and cannot be summed | Negative signs, parentheses, thousandths, or percent signs are formatted incorrectly | Select the cell and check if the editing bar contains quotes, spaces, or if there is a green triangle in the upper left corner of the cell |
| Only the first or last few pages contain data | Incomplete page range settings during conversion, or pagination causing table breakage | Check the number of lines and page numbers in the conversion results to confirm coverage of all pages |
Inspect and fix each item one by one
Check in the following order; the earlier steps usually resolve most issues, but the further you go, the more patience you need to be.
- Confirm the PDF type. Open the file and drag a section of text with the mouse. If it can highlight and copy readable text, it is the text version; If you can't select it, or if the copy is garbled or blank, it's a scanned copy or image-type PDF. This step determines how you will proceed from here.
- Perform OCR on the scanned materials first. Open the PDF in the OmniDoc desktop client (download link: download.omnidoc.top), use the OCR function of the PDF editor to recognize the text, then export it as Excel. After completing OCR, first randomly check a few paragraphs in the editor to confirm the recognition results match the original, then export the data. OCR results require manual verification, especially for amount, date, and serial number.
- When text is garbled, check the font. When copying text into Notepad with a PDF reader, if the notebook is also garbled, the problem lies with the PDF itself, usually because the font is not embedded or special encoding is used. In this case, switching to a different conversion method is often more effective than adjusting Excel settings. You can also try exporting a PDF from the original software, embedding the font, and then converting it.
- Check the page scope and hidden content. Confirm that the conversion is for all pages, not just a specific segment. Then check whether the table has merged cells, hidden rows and columns, cross-page headers, and numbers wrapped in text boxes or images. These details are easy to miss in ordinary text extraction.
- Unified numerical formatting. Converting numbers to text is usually due to the presence of
(1,234),1,234.5, or values with % symbols in the original table. After conversion, select a column of numbers, use "Split Column" or "Search" to replace the unified format, remove the thousandth comma, change the negative numbers in parentheses to negative, and then set the cells to numeric format. After making corrections, use the SUM function to perform a trial calculation and confirm that the sum works normally. - Use total rows and sample comparisons for verification. If the original table has total rows, use Excel's SUM to recalculate and compare whether the numbers match. If there are no total lines, randomly select 5 to 10 key numbers and compare them one by one with the original PDF. When deviations are found, go back to previous steps to find the cause, rather than manually modifying the results.
How to Prevent It from Happening Next Time
- Check the type before conversion. After getting the PDF, drag the text first to determine whether it's a text version or a scan, which can save you a lot of ineffective attempts.
- Prioritize conversion with original files. If you can obtain Word or Excel originals, exporting directly from the original is more reliable than converting from PDF backwards.
- Keep a copy of the original. Save the PDF original before conversion; you can review it anytime during verification, and do not rely on the converted file to determine the original data.
- Verify immediately after conversion. When there is a large amount of data, first check the total rows and the previous and following pages to confirm there are no systemic issues, then proceed with other content.
❓ Frequently Asked Questions (FAQ)
1. Does copying and pasting PDF tables into Excel also cause garbled text?
Yes. Copy-paste relies on the text layer of the PDF; if there are issues with the font or encoding, the paste will also cause garbled text. The scanned copy cannot be copied from text at all; OCR must be done first.
2. The number that comes out has a comma and cannot be summed. What should I do?
This is the number stored as text. Select this column, uniformly remove the thousandth comma and extra spaces, then set the cell format to numeric values. The negative sign and parentheses should also be unified, and after correction, use SUM for calculation.
3. Will all the numbers be correct after OCR?
You can't guarantee everything is correct. OCR recognition can be error-prone, especially when fonts are blurry or table lines are complex. Key figures such as amount, date, and serial number should be checked against the originals one by one.
4. There are many free conversion websites online. How do you choose?
When choosing a free tool, focus on several factors: file size and page limits, whether the output file has watermarks, how to handle uploaded files, and whether the rows, columns, and format of the converted table are preserved. If contracts, finances, or personal information are involved, it is recommended to confirm the privacy policy before deciding whether to upload it.
📝 Summary
When converting PDF to Excel with garbled text or missing numbers, first determine whether it is a text version or a scanned copy, then check font, page range, and numeric formatting in sequence. Most issues can be identified. After repair, you can use aggregate rows and sample comparisons to verify key data and confidently use the conversion results.
