To convert a PDF with a watermark to Excel cleanly, first find out whether the watermark is text or an image. Converters usually ignore an image watermark. A text watermark, such as a diagonal COPY or DRAFT, is read like any other text, and its letters land in the cells they cross. You can find and remove them afterwards.
Text watermarks versus image watermarks on a statement PDF
A watermark on a statement PDF is either text or an image. A text watermark is made of real characters in a font, often large, pale and set at an angle. An image watermark is a picture, such as a faint logo or a scanned stamp. Converters read text, so only text watermarks normally end up in your spreadsheet.
Both kinds look alike on screen. The word COPY across a page in light grey could be either. The difference is in how the file stores it:
- Text watermark. Stored as characters, just like the transactions. A converter has no way to know the word is decoration, so it reads the letters along with everything else.
- Image watermark. Stored as a picture behind or over the page. A converter reading text skips it, and the cells stay clean.
- Vector shape watermark. Some tools turn the watermark letters into outlines, which are shapes, not characters. A text converter skips these too.
Image watermarks become a problem in one case: when the page goes through OCR. Optical character recognition reads everything that looks like a letter, including a picture of the word DRAFT. If your file needs OCR, treat every watermark as text.
How does a watermark end up inside Excel cells?
A text watermark ends up inside Excel cells because converters place each character by its position on the page. A diagonal word crosses several columns and rows, so its letters are split up and each one joins the nearest cell. A date might come out as "03/14 C", a description as "RENT O", and an amount as "P 1,200.00".
The pattern follows the angle of the watermark. A word set diagonally from bottom left to top right puts its first letter low on the page in a left column and its last letter high in a right column. On a statement, that means a few rows each pick up a single letter, and the letters spell the word if you read them across the rows.
Large watermark fonts make this worse. A letter that is several lines tall can sit beside more than one row, so a converter may give it a row of its own. That shows up as a near-empty row with one character in it, between two real transactions.
The damage comes in three forms:
- Letters inside amounts. An amount with a stray letter is text, not a number, so it will not sum.
- Letters inside dates. A date with a letter attached stays text and will not sort.
- Extra rows. A row holding only a watermark letter adds a blank transaction and can throw off any count of rows.
Watermarks in non-Latin scripts behave the same way, but the stray characters are harder to spot by eye in a sheet of mixed text. If the statement itself is in another script, the guides to converting an Arabic PDF to Excel and converting a Chinese PDF to Excel cover the character issues that come on top of this.
Is the PDF watermark text or an image?
A one-minute search tells you whether a watermark is text or an image. Open the PDF and search for the watermark word, such as COPY. If the search finds it on every page, it is text and will reach your cells. No match means an image or a shape, which usually stays out.
Two more checks back up the search:
- Select across the watermark. Drag across a line of transactions where the watermark crosses it. If the copied text includes the watermark letters, the converter will see them too.
- Paste a line into a text editor. Copy one transaction row that crosses the watermark and paste it into a plain text editor. Stray letters in the middle of the row confirm a text watermark.
If the search finds the word only on some pages, the watermark is mixed. Some tools add text watermarks to new pages while older pages carry an image version. Plan for cleanup on the pages where the search hits.
Write down the watermark word and its letters. You will need the exact list when you clean the sheet: for COPY, the letters C, O, P and Y; for DRAFT, D, R, A, F and T.
Converting a PDF with a watermark you cannot remove
When you cannot remove the watermark, convert the PDF as it is and plan to clean the result. A watermark on a document you received belongs to the sender, and stripping it from the file is often neither possible nor appropriate. Cleaning the converted cells is the reliable route, and it does not change the original.
Before converting, take a few minutes to make cleanup easier:
- Ask for a clean copy. If the statement is yours, the bank's portal may offer a version without the watermark, or a CSV of the same transactions.
- Note which pages carry it. A watermark only on the first page limits the cleanup to those rows.
- Keep the original. A "COPY" watermark sometimes marks a duplicate statement issued on request. Keep the watermarked file as the record and use the spreadsheet only for your own work.
Some PDF editors can remove a watermark that was added with their own watermark feature, because they recognize it as a separate object. That works on files you made yourself. On a statement or report from someone else, the watermark is part of how the document was issued, and changing the file is not the fix.
FastBankConvert is built to read each statement page into rows with date, description, amount and balance, and it tells you before download if the balances do not add up. A stray letter that breaks an amount would show up in that check. You can get started with FastBankConvert to hear when trial conversions open.
Cleaning watermark letters out of converted cells
Cleaning watermark letters means removing the known stray characters from each column and turning amounts back into numbers. Because you know the watermark word, you know exactly which letters to look for. A helper column with SUBSTITUTE and TRIM removes them, and a filter catches rows that held nothing but a letter.
Start with the extra rows. Filter the date column for blanks, or for cells shorter than four characters, and look at what is left. Rows that hold only a single watermark letter can be deleted. Rows that hold a real transaction with an empty date are wrapped descriptions, so keep those.
Next, clean each column. The worked example below uses a COPY watermark. The values are invented for illustration.
| Column | Converted value (example) | Formula in the helper column | Cleaned value |
|---|---|---|---|
| Date | 03/14 C | =TRIM(SUBSTITUTE(A2,"C","")) |
03/14 |
| Description | RENT O MAIN ST | =TRIM(SUBSTITUTE(B2," O "," ")) |
RENT MAIN ST |
| Amount | P 1,200.00 | =VALUE(TRIM(SUBSTITUTE(C2,"P",""))) |
1200 |
| Balance | 8,415.20 Y | =VALUE(TRIM(SUBSTITUTE(D2,"Y",""))) |
8415.2 |
Two cautions apply to the table. First, the description column needs care, because the watermark letters are also ordinary letters. Replacing every O would turn "OFFICE" into "FFICE". Replace the letter only where it stands alone, with spaces around it, as the example does. Second, date cells may need converting to real dates after the letters go, for example with DATEVALUE or by retyping the format.
For amount and balance columns, the cleaning is safe to apply to every cell, because an amount rarely contains a letter. The exception is a credit or debit marker such as CR or DR, which shares letters with COPY and DRAFT, so turn those markers into signs first. Then nest one SUBSTITUTE per watermark letter, wrap the result in TRIM and VALUE, and fill it down. Any cell that still gives an error has a problem other than the watermark.
Finish with a total. Sum the cleaned amounts and compare with the statement's totals or closing balance. A match tells you no amount was damaged by the watermark or by the cleanup.
Paper stamps and scanned watermark copies
A paper stamp is a watermark added before scanning, such as a rubber stamp reading COPY or RECEIVED pressed onto a printed statement. After scanning, the stamp is part of the page image. OCR reads it like any printed word, so its letters can land in cells just as a text watermark would.
Stamps are often worse than digital watermarks for three reasons:
- They overlap the text. Ink over the transactions blurs the digits under it, so the amounts there may be misread as well.
- They are at odd angles. A hand-pressed stamp is rarely straight, so its letters spread over more rows.
- They are coloured. A red or blue stamp over black print can confuse recognition, which works best on clean black and white.
When you scan a stamped page yourself, good lighting and a flat page help OCR separate the stamp from the print. The guide to scanning documents with a Chromebook camera covers the setup for a clean scan. If you have the original unstamped statement, scan that instead.
When the watermark covers the statement amounts
Cleaning is not enough when the watermark covers the amounts themselves. A dense or dark watermark over the numbers can hide digits in the image, or interleave its letters with digits in the text. Then the problem is not extra letters but missing or wrong digits, which no formula can restore.
Signs that the watermark has damaged the data, not just added to it:
- Amounts with missing digits in rows where the watermark crosses, such as 1,2 0.00.
- Balances that do not follow from the previous row plus the transaction.
- OCR results that change from one attempt to the next on the same rows.
In those cases, get a clean source. For your own account, download the statement again or export the transactions as CSV. For a document from someone else, ask for an unwatermarked copy or the underlying data. If neither is possible, type the affected rows by hand from the page, and check them against the balance column as you go.