To extract numbers from a PDF to Excel as values you can sum, copy or convert the text, pull each figure out of its line, strip currency symbols, commas and units, and turn bracketed or trailing-minus amounts into negatives. Then test the column: a real number counts with COUNT and adds up with SUM.
Why do PDF numbers paste into Excel as text?
A PDF number pastes into Excel as text when anything around it stops Excel recognising a value. The PDF stores "1,240.00" as characters, not as a quantity. If those characters arrive with a currency sign Excel does not expect, a stray space, a footnote mark or a minus sign in the wrong place, Excel keeps the cell as text.
Text that looks like a number causes quiet errors. SUM skips it without a warning, so a column total can be short by exactly the cells that stayed as text. Sorting puts "1,240.00" before "86.50" because text sorts character by character. Lookups fail because the text "208" is not equal to the number 208.
Three signs tell you a column holds text:
- The figures sit against the left edge of the cell, while true numbers align to the right by default.
- A small green triangle appears in the corner, and the warning reads "Number Stored as Text".
- The status bar shows a Count but no Sum when you select the column.
The usual culprits, in rough order of how often they show up:
- A non-breaking space or a normal trailing space copied from the PDF.
- Thousands separators and currency symbols mixed with a regional setting that does not match them.
- Negative numbers written in brackets or with a minus sign after the digits.
- Units or markers stuck to the figure, such as "12 kg", "4.5%" with a footnote, or "1,240*".
Each has a fix below. If you need only a handful of values from a long document, such as a total or a date, pulling single fields out of a PDF covers label-based formulas that find them first.
Pulling numbers out of PDF sentences with REGEXEXTRACT
REGEXEXTRACT pulls numbers out of PDF text that mixes words and figures in one cell, such as "Paid 3 invoices totalling $4,090.00 on 03/31". It matches a pattern, so one formula can return the first figure, or all of them, from each line without any manual cutting.
Microsoft's REGEXEXTRACT reference gives the syntax as REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]). A return mode of 0 returns the first match, 1 returns every match as an array, and 2 returns the capture groups of the first match. It is available in Excel for Microsoft 365 on Windows and Mac.
A pattern for money-like figures:
=REGEXEXTRACT(A2,"-?\d{1,3}(?:,\d{3})*(?:\.\d+)?|-?\d+(?:\.\d+)?",1)
On the example sentence above, that spills "3", "4,090.00", "03" and "31" across the row. That is the catch with pulling every number: dates and counts come too. Two ways to get only the figure you want:
- Anchor on a label.
=REGEXEXTRACT(A2,"totalling \$?([\d,]+\.\d{2})",2)returns just "4,090.00", because only the capture group in brackets comes back. - Require two decimal places. Money amounts in most financial PDFs end in exactly two decimals.
"[\d,]+\.\d{2}"ignores the invoice count and the date.
The result is still text. Wrap it in the cleaning formula from the next section to turn it into a value. If your figures come in rows with a date, a description and an amount, building one pattern for the whole row is often easier. Using regex to split PDF lines into table rows walks through that.
Without Microsoft 365, the older route is to split the text with Text to Columns on spaces and then delete the columns you do not want. It is slower, and it breaks when the number moves position, but it works in any version.
Stripping currency symbols and units from PDF numbers
Stripping symbols and units means removing every character that is not part of the value, then converting what is left. SUBSTITUTE removes a known character, and VALUE turns the cleaned text into a number. Nested together, they handle the common case of a dollar sign and thousands commas in one formula.
=VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",",""),CHAR(160)," ")))
The CHAR(160) part replaces non-breaking spaces with normal ones, and TRIM then removes them. Non-breaking spaces are common in text copied from PDFs and web pages, and they are invisible, which is why a cell can look clean and still refuse to convert.
Units after the number
A figure such as "12 kg" or "3.5 hrs" has the unit after a space. =VALUE(TEXTBEFORE(TRIM(A2)," ")) keeps what comes before the first space. Move the unit into its own column if the units vary in one column. A column that mixes kilograms and pounds is not one that should be summed.
Percent signs
Excel understands "4.5%" as 0.045 when it converts it. That is usually what you want. If you want 4.5 instead, strip the percent sign with SUBSTITUTE before VALUE.
Footnote markers
Markers such as *, † or a superscript digit stuck to the end of a number break conversion. The superscript digit is the dangerous one: "1,240²" may come across as "1,2402" and convert to a wrong value without any error. Look for figures that are one digit longer than their neighbours, and check them against the PDF.
How should negative PDF numbers be kept negative?
Negative PDF numbers stay negative only if you convert their marker into a leading minus sign. Financial PDFs write negatives in at least four ways: a leading minus, brackets around the figure, a minus after the digits, or a "CR" or "DR" suffix. Each needs its own rule, applied before VALUE, or the sign is lost.
| Raw text (example) | Meaning | Formula on A2 |
|---|---|---|
-86.50 |
Negative | =VALUE(A2) |
(86.50) |
Negative | =-VALUE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")) |
86.50- |
Negative | =-VALUE(LEFT(A2,LEN(A2)-1)) |
86.50 CR |
Depends on the document | =VALUE(TEXTBEFORE(A2," ")), then apply the sign rule |
$1,240.00 |
Positive | the stripping formula above |
When a column mixes styles, one formula can test for each marker:
=LET(t,TRIM(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")),
IF(LEFT(t)="(",-VALUE(MID(t,2,LEN(t)-2)),
IF(RIGHT(t)="-",-VALUE(LEFT(t,LEN(t)-1)),VALUE(t))))
The CR and DR suffixes need a decision, not just a formula. On a bank statement, "CR" often marks money in. On a credit card statement, it often marks a payment or refund that reduces what you owe. Decide which direction is negative for your sheet, write it down in a cell note, and apply it consistently.
Text to Columns has its own answer for one style. In the wizard's last step, the Advanced button has a "Trailing minus for negative numbers" option, which converts "86.50-" during the split.
Two columns for money in and money out are another common layout. Keep them as two positive columns, and add a third that subtracts one from the other. Merging them into one signed column is fine, but do it with a formula, so the original columns remain for checking.
Decimal commas and other PDF number formats
A decimal comma is the convention in much of Europe and South America, where "1.240,50" means one thousand two hundred forty and a half. If your Excel uses a decimal point, VALUE reads that string wrongly or not at all. NUMBERVALUE fixes it by letting you name the separators.
=NUMBERVALUE(A2,",",".")
The second argument is the decimal separator and the third is the group separator in the text, regardless of your own settings. "1.240,50" returns 1240.5.
Other formats that trip up conversion:
- Space as a thousands separator. "1 240,50" uses a space, sometimes a narrow non-breaking one. Remove it with SUBSTITUTE on both CHAR(32) and CHAR(160) before NUMBERVALUE.
- Apostrophes. Swiss documents may write "1'240.50". Remove the apostrophe.
- Numbers in thousands. Annual reports often say "in thousands" at the top of a table. The figure 1,240 means 1,240,000. Keep the figure as printed and note the scale in the header, or multiply in a separate column. Turning an annual report PDF into Excel covers the other quirks of report tables.
- Dates that look like numbers. "03.05" may be a date or a decimal. Only the column heading in the PDF can tell you.
Handwritten figures are a separate problem entirely, because they must be recognised as characters before any of these rules apply. Converting a handwritten PDF to Excel explains what OCR can and cannot read there.
A check that every extracted PDF number is a value
A check that every extracted number is a value compares how many cells hold numbers with how many hold anything at all. If COUNT and COUNTA disagree on the same column, some cells are still text. A second check against a printed total proves the values are right, not just numeric.
Put these under the column, or in a spare cell:
=COUNTA(B2:B200)-COUNT(B2:B200)
=SUM(B2:B200)
The first should be zero. If it is not, filter the column with a helper formula =ISTEXT(B2) to find the cells that did not convert, and fix them with the formulas above.
The second should match a total printed in the PDF. A total that is off by a round amount often points to a single figure with a misread digit. A total that is off by twice one figure points to a sign error: a negative read as positive changes the sum by twice its size. Search the column for that figure first.
Finally, keep the raw text column next to the cleaned one until you have checked the total. If anything is wrong, you can see what each formula started from.
If the numbers come from bank statements, FastBankConvert is built to put dates, descriptions, amounts and balances in their own columns, and it tells you before download if the balances do not add up. It has not launched yet, so the get-started page is the place to leave your email.