Extract Data from PDF to Excel, One Field at a Time

Quick Answer: Get the PDF's text or tables into Excel first, then pick out the fields. Use Data > Get Data > From PDF when the values sit in a table, and filter rows in Power Query. When they sit in running text, paste or import the text and use TEXTBEFORE and TEXTAFTER, or Flash Fill, to cut out each value. Scanned PDFs need OCR before any of this works.

Extracting specific data from a PDF means taking a handful of values, such as an invoice number, a total, a date or a customer name, and placing each one in its own column, rather than copying the whole page. The method depends on whether the PDF has a text layer and whether the values sit in a table or inside sentences. Excel can do all of it for free.

Is your PDF text, a table, or an image?

Spend thirty seconds on this before choosing a method.

The most common mistake is treating a label-value document like an invoice as a table. Converters then produce a grid with the label in one cell and the value two columns over on a different row, which is harder to work with than the raw text.

How do you pull specific rows from a PDF table with Power Query?

When the data you want lives in a table, let Power Query find the table and then throw away everything you do not need.

1. In Excel for Microsoft 365 on Windows, choose Data > Get Data > From File > From PDF and open the file.
2. In the Navigator, pick the table that holds your values and click Transform Data rather than Load.
3. Use the filter arrow on a column to keep only the rows you want, for example rows where Description contains "Freight", or rows where Amount is greater than 1,000.
4. Right-click and Remove Other Columns to keep only the fields you need.
5. Set data types (date, decimal number) from the column header icons.
6. Close & Load.

Every step is recorded, so when next month's PDF arrives you point the query at the new file and refresh. Microsoft's PDF connector reference also lists options for reading only certain pages and for tables that continue across pages.

To pull the same field from many PDFs, such as the total from 200 invoices, use Get Data > From File > From Folder instead. Power Query applies the same steps to every PDF in the folder and stacks the results, one row per file.

How do you extract values from text with TEXTBEFORE and TEXTAFTER?

When the PDF holds label-value text, the job is to get each document's text into a cell and slice it. Suppose cell A2 contains:

Invoice Number: INV-20431 Invoice Date: 03/14/2026 Customer: Harbor Supply Co Total Due: $1,284.50

These formulas pull out each field:

TEXTAFTER drops everything up to the label; TEXTBEFORE then stops at the next space or the next label. Microsoft's TEXTBEFORE reference lists the optional arguments worth knowing: instance_num picks the second or last occurrence (use a negative number to count from the end), match_mode set to 1 ignores case, and if_not_found returns your own value instead of #N/A when a label is missing. The functions are in Microsoft 365 and Excel 2024. In older versions, MID and FIND do the same job with more typing.

Getting the text into cells in the first place: for one document, select all in the PDF viewer, copy and paste into a cell or column. For many, Power Query's PDF import exposes each page as its own entry you can expand into text, or a PDF editor can export to plain text.

When is Flash Fill quicker than formulas?

Flash Fill learns a pattern from an example you type. If column A holds pasted lines such as Order 5531 shipped 04/02/2026 to Denver, type 5531 in B2, then press Ctrl+E or choose Data > Flash Fill. Excel fills the rest of the column with the order numbers it sees in the same position, per Microsoft's Flash Fill guide.

Flash Fill is ideal for a one-off list where the pattern is regular. Its limits:

• It produces values, not formulas, so it does not update when the source changes.
• It guesses. Scan the result for rows where the pattern broke, typically where a name has an extra word.
• It works on one column of source text at a time.

For a recurring task, write the formulas or build it in Power Query instead.

How do you extract the same fields in Power Query without formulas?

Power Query has its own text functions that mirror the worksheet ones. After loading the text into a query, add a custom column with a formula such as:

Text.BetweenDelimiters([Column1], "Total Due: $", " ")

Microsoft's Text.BetweenDelimiters reference shows the optional index arguments for picking the second occurrence of a label or counting from the end. Because the step is part of the query, it reruns on every refresh, which makes this the most maintainable approach when the source is a folder of similar PDFs. Change the new column's type to a decimal number before loading so totals sum.

What if the PDF is scanned?

None of the above works on a picture of text. Run OCR first: Acrobat Pro's text recognition, Excel's Data from Picture for a single page, or another OCR service. Then check the extracted values carefully, because OCR errors cluster in exactly the characters you are extracting: digits, decimal points and currency symbols. Our guide to OCR from PDF to Excel goes through the options and how to proof the numbers.

When should you use a purpose-built parser instead?

Formulas break when layouts vary. If the field you want sits in a different position on each document, or the documents come from many sources, you spend more time maintaining extraction rules than extracting.

Bank and credit card statements are a good example. Each bank labels and positions things differently, and the data you want is not one field but every transaction plus the opening and closing balance. A statement parser reads those directly. QuickBankConvert, for instance, outputs one row per transaction with date, description, signed amount and running balance, and shows the account number and statement period above the preview, so the "specific data" arrives already separated. Our explainer on how statement parsing works covers why statements resist generic extraction, and the statement to Excel page shows the output columns.

Related guides

• Importing a whole PDF into Excel
• Reading scanned PDFs into a sheet
• What makes statement PDFs hard to parse