How to convert Excel numbers exported from PDF into text and convert them back to numerical values in batches

Key points of this article

Numbers are left-aligned, have a small green triangle, and sum to 0. They are usually text-type numbers. Confirm with ISNUMBER first.
After selecting the entire column, the conversion prompt given by Excel is fast and suitable for most columns that only contain spaces or ordinary numbers.
Thousand commas, non-breaking spaces and negative numbers in parentheses must be cleared first, otherwise the conversion will fail or the values will be incorrect.
Setting a cell to a numerical format only changes the display. It does not convert existing text into a numerical value. It must be converted again.
The scanned PDF must be OCRed first, and the key numbers in the OCR result still need to be checked against the original.

When exporting Excel with PDF, we often encounter this situation: the table looks correct and the numbers are there, but the sum result is 0, or there is a small green triangle in the upper left corner of the cell. This means that Excel treats these numbers as text, rather than numerical values that can be calculated. When many people do "extract data from pdf to excel", they think that they can use it directly after exporting. In fact, the intermediate format conversion is the key. Similarly, pasting the results of "How to copy pdf table data to excel" into the table can easily lead to the same problem.

This article is written for people who need to move financial statements, experimental data or transcripts from PDF to Excel to continue calculations. After reading, you can determine why the numbers turned into text, and use several batch methods to convert the values back at once without having to re-enter them cell by cell.

Turning numbers into text doesn't necessarily require redoing the entire column. In most cases, it can be solved by first finding out the cause and then doing a batch conversion of the entire column. Only a few columns need to clean symbols first, or need to do OCR first, and the processing is not complicated.

The numbers cannot be calculated after exporting. How can I convert the values back in batches? One sentence conclusion

First determine why the number is text, and then process the entire column uniformly. Usually, the value can be converted back to the value in one operation, without the need to modify it one by one.

your situation	Recommended practices	reason
There is a small green triangle in the upper left corner of the cell, and the numbers are aligned to the left	Select the entire column, click the exclamation point prompt that appears, and choose Convert to Number in the prompt.	Excel has recognized text numbers and batch conversion is fast.
There are thousands commas or spaces in the number	First use find and replace to remove commas and spaces, then convert	Delimiters prevent Excel from recognizing them as numeric values
The amount column has brackets to indicate negative numbers, such as (1,234.50)	First change the brackets to negative signs and remove the commas, then convert	The format of brackets and negative signs needs to be unified before calculation
The exported PDF is a scanned copy, and the numbers are messed up or missing.	Do OCR in PDF editor first, then export to Excel	The scanned document has no text layer and the export result is unreliable.
Only a few cells have problems	Enter 0 in a blank cell and copy it, then selectively paste "Add"	Add 0 to text numbers to get the numerical value directly

First determine what kind of problem it is

Different symptoms correspond to different causes. Use the table below to confirm before choosing a method of practice.

symptoms	Common causes	How to confirm
The summation result is 0 or significantly smaller than	Numbers are text	Enter =ISNUMBER(A2) in a blank cell and return FALSE indicating that it is text.
The numbers are aligned to the left, and there is a small green triangle in the upper left corner of the cell.	text number	Select the cell and look at the edit bar and alignment. Excel will usually give you a prompt.
There are commas or spaces between numbers	Separators mixed in numbers	Click into the cell and look at the edit bar to see if a comma or space is sandwiched between the numbers.
Still unable to convert after cleaning	There are invisible characters such as non-breaking spaces	Use the LEN function to compare the number of characters. The characters that are more than the number you see are hidden characters.
Numbers are messed up or missing after exporting the scanned document	No text layer, relies on OCR	Open it with a PDF reader and try to select a number. If you can't select it, just scan it.

Check and repair item by item

Process them in order from easy to difficult. Most situations can be solved in the first three steps.

First confirm whether the PDF is a scanned copy. Open the original file with a PDF reader and try to select a number with the mouse. If it can be selected, it means there is a text layer and can be exported directly; if it cannot be selected, you need to do OCR first. The PDF editor of OmniDoc desktop client (download.omnidoc.top) supports OCR and can export PDF to Excel. Both steps can be completed in the same tool.
Check whether it is a text number. Enter =ISNUMBER(A2) in the blank column and fill it down. The cell that returns FALSE is the text.
Convert the entire column. Select the column, and if an exclamation point prompt appears next to the cell, select Convert to Number in the prompt. This is a fast batch method, suitable for columns containing only ordinary numbers.
Remove commas and spaces. To use Find and Replace, enter a space in "Find content", leave "Replace with" blank, and replace all; the same goes for commas. Be careful to only delete the comma in the thousands place and don't delete the decimal point by mistake.
Use formulas to batch convert. If there are still stubborn hidden characters, enter =VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),""),",","")," ","")) in the side column and fill it down, remove non-breaking spaces, commas and ordinary spaces and convert it to a numerical value. After confirming that the result is correct, copy the column and then selectively paste it as a value to overwrite the original column.
Use the sorting function to re-identify. Select the entire column, use Excel's built-in column sorting function, click Finish directly, and Excel will re-judge the format based on the current content. This step can fix many remaining formatting problems.
Check the results. Sum the entire column and compare it with the total row of the original PDF; check a few more rows to make sure there are no errors in the decimal point position and negative sign. It is recommended to check important figures such as amount and quantity with the original one by one, especially the content that has been OCRed. OCR may identify the numbers incorrectly.

How to avoid it happening again next time

Determine PDF type before exporting. Export the text version directly, and do OCR on the scanned version first. Don’t wait until the form is finished to find out that the numbers are wrong.
Add a column to check immediately after exporting. For example, use ISNUMBER to check key columns on the right side of the table, or sum the amount column directly to find textual numbers early.
Clean first, then format. Changing the "regular" format to a numeric format will only change the display. Existing text will not be converted to numeric values, so the content must be converted first and then formatted.
Numbered columns hold text. If columns such as contract number, student number, and identity number are converted to numerical values, leading zeros will be lost. For example, 001 becomes 1. Do not perform numerical conversion on such columns.
Keep original copies of important data for comparison. Small errors may occur during the export and conversion process, and key figures must be checked item by item in the final table and PDF.

❓ Frequently Asked Questions (FAQ)

1. After setting the cell format to numerical value, the numbers still cannot be summed. Why?

Cell format only determines the display mode and does not change the content. Numbers that are already text need to be converted back into numeric values through conversion, columnarization, or formulas.

2. After converting text numbers into numerical values, will the leading zeros be lost?

meeting. For example, the number 001 will be displayed as 1 after being converted into a numerical value. Numbering columns should remain text, and only columns that need to be calculated, such as amounts and quantities, should be converted.

3. After the scanned PDF is exported, all the numbers are wrong. Can it be converted directly?

It cannot be converted directly, OCR should be done first. OCR results may have errors, and key figures must be checked with the original before being used in calculations.

4. When the number is very small, is it necessary to use the batch method?

When the quantity is small, you can manually modify it, or use the special paste "add" method to quickly convert. The batch method is more suitable for the entire column of data.

5. Percentages and dates cannot be calculated after exporting, what should I do?

Percentage can be removed by removing the percent sign, then dividing the result by 100, or formatting it as a percentage. For dates, make sure that the order of year, month, and day and the delimiters are consistent, otherwise Excel will treat it as text.

📝 Summary

Excel numbers exported from PDF cannot be calculated. Most of them are text numbers. Use ISNUMBER to confirm first, and then convert, clean or split the entire column. Scans need to be OCRed first, and important numbers still need to be checked against the originals. Once you master this process, when you extract data from PDF to Excel, you can change it from "can be exported" to "can be directly calculated".

Open OmniDoc and work with your spreadsheet file