# Flash Fill PDF Data: Teach Excel the Pattern Once

- Author: FastBankConvert (https://www.fastbankconvert.com)
- Published: 2026-09-08
- Updated: 2026-09-08
- Category: Tables and data from PDFs
- Canonical: https://www.fastbankconvert.com/blog/flash-fill-pdf-data

> Use Flash Fill on PDF data: type one or two examples, let Excel pull dates, amounts or codes out of pasted lines, and check where it guessed wrong.
Flash Fill can pull PDF data out of pasted lines once you show it what you want. Paste the PDF text into one column, type the value you need from the first line in the next column, and press Ctrl+E. Excel copies the pattern down every row. Then check the rows it filled, because it guesses.

## Setting up pasted PDF lines for Flash Fill

Flash Fill needs the source text in one column, one PDF line per row, and an empty column directly beside it for the results. It learns from the cells next to your example, so anything between the source and the result column, or blank rows inside the data, weakens its guess.

Paste the PDF text into column A. If the paste lands with each line in its own row, you are ready. If whole paragraphs land in single cells, split them into lines first. Then:

- Delete blank rows inside the block. Flash Fill stops at gaps.
- Remove page headers and footers that repeat every page, or at least know where they are, since Flash Fill will try to extract a value from them too.
- Give column B a header naming the field you want, such as "Date".

[Microsoft's Flash Fill guide](https://support.microsoft.com/en-us/office/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7) lists Excel 2016 and later, including Microsoft 365 and Excel for Mac 2021 and 2024. It says you can run it with Ctrl+E or from Data > Flash Fill. If no preview appears as you type, the automatic option may be switched off, and the guide shows where to turn it on in Excel's advanced editing options.

## Teaching Flash Fill a PDF field with one or two examples

To teach Flash Fill a field, type the exact value you want for the first row, then press Ctrl+E. If the filled column is wrong, correct one of the wrong rows and press Ctrl+E again. Each correction is a new example, and Flash Fill updates its guess to fit all of them.

Pick a first example that shows the rule clearly. If some lines have a dollar sign and some do not, use a line with one, so Excel sees that the sign should be dropped. If some descriptions are long and some short, a long one tells it more about where the field ends.

Here is a worked example with six made-up pasted lines. The goal is the amount at the end of each line. The first two results were typed, the rest came from Flash Fill.

| A: pasted PDF line (example) | B: amount |
| --- | --- |
| `03/02 Office chairs order 4471 $1,240.00` | 1,240.00 (typed) |
| `03/05 Customer payment INV-208 $3,500.00` | 3,500.00 (typed) |
| `03/09 Freight $86.50` | 86.50 |
| `03/12 Parking 2 hrs $14.00` | 14.00 |
| `03/14 Refund order 4471 ($120.00)` | 120.00) |
| `03/18 Bank fee $5.00` | 5.00 |

Five rows look right. The refund row is the useful failure: Flash Fill learned "everything after the dollar sign" and kept the closing bracket, and it lost the fact that a bracketed amount is negative. Correct that row to `-120.00` and press Ctrl+E again, and it may learn the rule, or it may not. Read the result either way.

The same approach works for dates, codes and names. Typing `INV-208` beside the payment line teaches it to pull invoice references, and typing `03/02` pulls dates. One field per column works best. Email addresses are a common target, and [pulling email addresses out of PDF text](/blog/extract-email-addresses-from-pdf-to-excel) covers a pattern that is more reliable than a guess for that job.

## Where does Flash Fill guess wrong on PDF text?

Flash Fill guesses wrong on PDF text when the lines vary in ways your examples did not show. It infers a rule from a few rows and applies it to all of them, without telling you which rows fit badly. Plausible but wrong values are the danger, not errors.

The usual trouble spots:

- **Numbers inside descriptions.** "Parking 2 hrs" worked above, but a line such as "Order 4471 2 units $80.00" may give it a reason to pick the wrong number.
- **Missing fields.** A line with no amount, such as a wrapped description, still gets a value, often a fragment of text.
- **Mixed formats.** Dates written as 03/02 on some lines and Mar 2 on others, or amounts with and without the dollar sign.
- **Signs and brackets.** Negatives in brackets or with a trailing minus are rarely carried over correctly.
- **Header and footer lines.** "Page 1 of 3" may produce a "3".

A quick check catches most of them. Sort a copy of the result column and look at the top and bottom. Odd values cluster at the ends: a lone bracket, a word, a number far larger than its neighbours. Then compare the row count of values with the number of real data lines.

Links and email addresses embedded in a PDF are a special case, since what you see on the page may not be the address behind it. [Extracting hyperlinks from a PDF to Excel](/blog/extract-hyperlinks-from-pdf-to-excel) explains why.

## Is Flash Fill output safe to sum and sort?

Flash Fill output is safe to sort but not always safe to sum. The results are fixed values, not formulas, so they will not update if the source changes. And because Flash Fill copies characters, an amount can come back as text that looks like a number, which SUM silently skips.

Two checks before you rely on the column:

1. Select it and look at the status bar. If it shows a Count but no Sum, the values are text. Convert them with Text to Columns, or with `=VALUE(B2)` in a helper column.
2. Compare the sum with a total printed in the PDF. A gap equal to one amount means one row was wrong; a gap of twice one amount means a sign was lost.

Because the result is static, Flash Fill suits a one-off job. Next month's PDF needs the examples typed again. If the job repeats, formulas or a short script hold the rule permanently, and [a Python script for pulling data out of PDFs](/blog/extract-data-from-pdf-to-excel-python) is a sensible next step. For bank statements, a fixed layout such as a [bank statement Excel template](/blog/bank-statement-excel-template) gives the cleaned columns a home.

FastBankConvert takes a different route for bank statements: it is built to put dates, descriptions, amounts and balances in their own columns without any typed examples. It has not opened yet, and [the get-started page](/get-started) is where to sign up for launch news.