Find NI numbers, bank details and dates of birth in a Google Sheet™
Personal data turns up in sheets that were never meant to hold it: a rota with a column added "just for payroll", a sign-up sheet with a date of birth field, a notes column where someone pasted a bank detail.
By hand
National Insurance numbers. Two letters, six digits, a letter A to D, often with spaces. In a helper column:
=REGEXMATCH(UPPER(SUBSTITUTE(A2," ","")),"^[A-Z]{2}[0-9]{6}[A-D]$")
This matches the shape only. Some letter pairs are never issued, so a match may be a false positive.
Bank details. UK sort codes look like 12-34-56 and account numbers have 8 digits. Look for the two side by side:
=REGEXMATCH(A2,"^[0-9]{2}-?[0-9]{2}-?[0-9]{2}$")
Dates of birth. Look at headers first (DOB, Date of birth, Birthday). Then check that the dates in the column fall in a plausible range for people.
Sort each helper column so the TRUE rows come together. Delete the helper columns before you share.
What by-hand checks miss
- Cell notes, which are not in the grid your formulas read.
- Hidden tabs, rows and columns you have not unhidden.
- White-on-white text and numbers formatted to display as blank.
- Ten-digit numbers that look like NHS numbers but are order numbers, and the reverse. A shape match alone cannot tell them apart.
A check that reads all of it
Personal Data and Sharing Check™ tests values against their rules, such as the check digit on NHS and card numbers, as well as their shape. It also reads hidden tabs, notes and disguised text. A finding says the data may be there and shows a masked sample, so you can judge it without putting the full value on screen.
Check before you share
Personal Data and Sharing Check™ scans a Google Sheets™ file for personal data, including hidden tabs, notes and disguised text, and shows who can already see it. It shows masked samples only, never the full value. Checking is free, and your data stays in your browser.
Install from the Google Workspace Marketplace™
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. App Garden™ is not affiliated with or endorsed by Google. This is data-minimisation and sharing-hygiene guidance, not legal advice.
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Personal Data and Sharing Check is not affiliated with or endorsed by Google.