Stop Manually Redacting PII in Excel — Automate with Regex Pattern Scanning

Manual miss vs automated catch

A human scanning a phone column overlooks an email pasted into a notes cell. The scanner gets both because it scans every value, not just the obvious columns.

Input row:
{ "Phone": "+1 415-555-0142",
  "Notes": "call back, prefers email sam@acme.io" }

Manual (phone column only): Notes email MISSED.

Automated:
{ "Phone": "[REDACTED_PHONE]",
  "Notes": "call back, prefers email [REDACTED_EMAIL]" }

Counts: { phone: 1, email: 1 }

Naive find/replace breaks data; the scanner doesn't

Replacing every 16-digit run would wreck order IDs. Luhn validation means only the real card is touched.

Find/replace '16 digits' -> redacts EVERYTHING:
  4242424242424242 -> [X]   (card, OK)
  1000000000000001 -> [X]   (order id, WRONG)

Scanner (Luhn-gated):
  4242424242424242 -> [REDACTED_CARD]
  1000000000000001 -> 1000000000000001  (kept)

Determinism for review and CI

Running the same file twice gives identical output and counts, so a reviewer can diff and trust the result.

Run 1 counts: { email: 1840, ssn_us: 12, phone: 1622 }
Run 2 counts: { email: 1840, ssn_us: 12, phone: 1622 }

Identical -> safe to gate a pipeline on these numbers.
(Manual redaction is never reproducible like this.)

Where automation still needs a human

An unusually punctuated phone and a free-text name slip past the patterns. The counts hint at it; a quick manual pass closes the gap.

Input:
{ "Contact": "Priya N.", "Tel": "415.555.0142x99" }

Output:
{ "Contact": "Priya N.", "Tel": "[REDACTED_PHONE]x99" }

Name not detected; extension fragment 'x99' left behind.
Manual fix: drop Contact column; verify Tel residue.

One pass replaces six manual sweeps

What would be six separate Ctrl+F passes (email, phone, SSN, card, IBAN, NI) is a single run.

Manual: 6 separate Find passes, per column, per pattern.
Automated: 1 run, all six detectors, every cell.

Findings: { counts: { email: 1, phone: 1, ssn_us: 1,
  credit_card: 1, iban: 1, ni_uk: 1 }, itemsRedacted: 6 }

Credit-card detection only fires when the digit run passes the Luhn checksum, and IBAN only when the mod-97-10 check passes. A 16-digit order number or a typo'd card that fails Luhn is left exactly as-is. An SSN-shaped value in a banned block (e.g. 000-12-3456 or 666-...) is likewise skipped by design — those are never valid SSNs.

The reader flattens wb.SheetNames[0] to text and scrubs that. PII sitting on a second tab (a hidden 'Contacts' sheet, an 'Audit' tab) is not touched. Split multi-tab workbooks first, or scrub each sheet's data separately, and remember to clear residual sheets with the hidden-sheet destroyer.

Feeding an .xlsx in does not return an .xlsx out. The first sheet becomes a JSON array of row objects, the scrub runs, and you download <name>-scrubbed.xlsx whose contents are scrubbed text/JSON. Treat it as a redacted data dump, not a styled, multi-sheet workbook — formulas, formatting, and extra tabs are gone.

The phone matcher targets a broad international shape (+CC then 2–4 digit groups split by space/dot/dash). Tightly packed or unusually punctuated locals (e.g. parenthesised area codes glued to text, vanity numbers like 1-800-FLOWERS) can slip through. Check the per-category count after a run and spot-check the output.

There is no name/address NLP here — the six detectors are all pattern-and-checksum based. John Smith, 14 Mill Lane stays in the clear. For free-text identifiers you must redact those columns by other means (drop the column, or generalise it) before sharing.

The email pattern matches anything shaped like local@domain.tld regardless of context, so mailto:sam@acme.io and //user@host.example/path both get the address portion replaced with [REDACTED_EMAIL]. This is usually what you want, but it can rewrite a host string you meant to keep.

Because the generic phone pattern runs after IBAN/card/SSN/NI, any long digit group those earlier detectors rejected (failed Luhn, wrong block) is still eligible to match as a phone number if it fits the loose phone shape. Review counts if a value you expected to keep was tagged [REDACTED_PHONE].

A Free-tier scan stops at 5 MB / 10,000 rows / 1 file. Larger exports return a limit error before any scrubbing runs. Split the export, trim to the needed columns, or move to a higher tier (Pro 50 MB / 100k rows, Developer 500 MB / unlimited).

The scan is exhaustive over the patterns it knows, but unusual phone punctuation, free-text names/addresses, and second-sheet data are outside its reach. Use the per-category counts as a review checkpoint and do a short manual pass on edge formats before sharing — automation reduces the manual work, it doesn't eliminate judgement.