OmniDocExtract Numbers from PDF to Excel Without Losing Separators
Export PDF figures to Excel and keep thousands separators, decimals and currency symbols usable as numbers, with the cleanup steps to run after export.
You have a PDF full of figures: an invoice summary, a financial table, or a price list. You want those values in Excel so you can sort, total and chart them. The export may complete, but the numbers can arrive looking like numbers and still act like text. Amounts sit on the left side of the cell, a currency-prefixed value won't add up, and "(340)" stays a string that formulas skip.
This guide is for anyone who needs to extract numbers from PDF to Excel and use them in calculations afterward. You will export the PDF with OmniDoc, find the columns Excel reads as text, and clean up separators, currency symbols and negative signs so the values work in formulas.
How do you extract numbers from a PDF to Excel and keep them usable? The short answer
Export the PDF to Excel first, check which numeric columns Excel reads as text, then strip separators and symbols and apply a number format at the end.
| Your situation | What to do | Why |
|---|---|---|
| The PDF has selectable text | Export it to Excel from the PDF editor in the OmniDoc desktop client | Text is read directly, so figures stay in their rows and columns |
| The PDF is a scan | Run OCR in the PDF editor before you export | Scanned figures are pictures until OCR turns them into text |
| Figures include commas, currency signs or spaces | Clean the column, then convert it to numbers | Excel only calculates on values it reads as numbers |
| Negatives are in parentheses | Convert them to minus values with a formula | Excel does not read "(340)" as −340 by default |
| Values carry a % sign | Convert with VALUE, then apply a percent format | The stored value must be the decimal equivalent for math to match the PDF |
Before you start
- Check whether the PDF has real text. Try selecting a line of text with your cursor. If the words highlight one by one, the text is present. If nothing selects, the page is probably a scan and needs OCR first, because exporting an image gives you no usable figures.
- Look at a sample of the source figures. Note how negatives, currencies and percentages are written, for example "(340)", "-340", an amount with a currency sign in front, or a value with a % sign. Each pattern needs a different fix, so knowing them early saves rework.
- Confirm your regional settings in Excel. In a setup where the comma is the decimal sign, a value written with a comma means something different than in a US setup, where commas separate thousands. Match your cleanup to how the source writes its numbers.
- Keep the original PDF and the raw export. If a cleanup step goes wrong, you can run it again from the untouched export instead of re-exporting.
- Install the desktop client if you do not have it. The PDF editor, including export to Excel and OCR, is part of the OmniDoc desktop client, which you can get at download.omnidoc.top.
Extract numbers from PDF to Excel step by step
- Run OCR if the PDF is scanned. Open the PDF in the PDF editor and apply OCR so the figures become selectable text. Export works from text, so skipping this step can leave you with empty or garbled cells.
- Export the PDF to Excel. In the PDF editor, choose the export option for Excel and save the workbook. Keep this file as your raw copy, because the cleanup steps below change values in place.
- Find the cells Excel reads as text. In a blank cell next to a figure, type =ISTEXT(B2) and fill it down the column. TRUE means the cell holds text, and FALSE means Excel reads it as a number. Columns full of TRUE need cleanup.
- Remove thousands separators. Select only the columns where commas separate thousands, then use Excel's Find and Replace to replace the comma with nothing. Check a few cells afterward to confirm the decimal point survived.
- Remove currency symbols and hidden spaces. Replace the currency sign with nothing. PDF exports often include non-breaking spaces that look like normal spaces but block conversion, so use a formula such as =TRIM(SUBSTITUTE(B2,CHAR(160),"")) to remove them.
- Convert the cleaned text to numbers. Use Excel's Text to Columns tool on the cleaned column with its default settings, or write =VALUE(B2) in a new column and paste the results as values. Either way, the cells should now align to the right and respond to SUM.
- Handle negatives in parentheses and percentages. For "(340)", use a formula like =-VALUE(SUBSTITUTE(SUBSTITUTE(B2,"(",""),")","")), which turns it into −340. For a value with a % sign, VALUE returns the decimal equivalent, so apply a percent format and the display will match the PDF.
- Apply number formats last. Once the values are numeric, set the column to a currency or number format with the decimal places you need. The format only changes how the value displays, so the total and charts still work.
Check the result
Once the column is converted, test it before you build anything on top of it. Use the table below to find problems early.
| Check | How to see it | If it's wrong |
|---|---|---|
| Numeric type | Numbers align to the right, and =ISTEXT() returns FALSE | Repeat the conversion on that column, and look for stray spaces |
| Column total | Add a SUM over the column and compare it with the total row in the PDF | Find the text cells that SUM skips and convert them |
| Decimal places | Compare five or six cells with the source | If a decimal was lost, undo the separator step and redo it on a copy |
| Negative values | Filter for cells that were in parentheses in the PDF and confirm they show a minus sign | Re-run the negative conversion on those cells only |
| Row alignment | Compare labels with figures for three to five rows | Re-export, and check whether the PDF table has merged cells |
Exports and OCR can misread characters, especially in small or low-quality scans. Check any figure that matters for money, tax or reporting against the original PDF before you rely on it.
❓ FAQ
1. Why are my numbers still left-aligned after I export?
Excel left-aligns text, so the column is still storing the figures as text. Run the ISTEXT check, then remove separators and symbols before converting the column again.
2. Can I keep the currency symbol and still sum the column?
Yes. Convert the values to numbers first, then apply a currency format. The currency sign appears on screen, but the cell still holds a number that SUM can add.
3. Should I convert the whole spreadsheet?
No. Convert only the numeric columns. Leave identifiers such as invoice numbers, ZIP codes or account codes as text, because converting them can drop leading zeros or change long numbers.
4. What if OCR misreads some digits in a scanned PDF?
Compare the affected cells with the scanned page, correct them by hand in Excel, and recheck the column total. Treat OCR output as a draft until you have confirmed the figures that matter.
📝 Summary
To extract numbers from PDF to Excel and keep them usable, export from a PDF with selectable text, check which columns Excel reads as text, and clean separators and symbols before you apply number formats. Test each column with SUM against the source totals so you can trust the numbers before you build on them.
