Validate IBANs, VAT IDs, and SKUs

Northwind’s vendor list carries IBANs, VAT registration numbers, and internal SKUs, all typed in by hand. A single transposed digit in an IBAN sends a bank transfer to the wrong place — or bounces it back days later. A malformed VAT ID fails an accountant’s reconciliation. A SKU that does not match the house format quietly breaks a stock report.

These three custom functions catch all of that the moment someone fills a cell. VALID_IBAN runs the real ISO checksum, not just a length check; VALID_VAT_GB matches the published UK VAT formats; and VALID_SKU enforces Northwind’s own NW- pattern. Each returns TRUE or FALSE, so you can wrap them in conditional formatting and let the bad rows light up red.

What you’ll need

• A Google Sheet with a vendor or product list — IBANs in one column, VAT IDs in another, SKUs in a third.
• A spare column next to each identifier for the validation result, or a conditional-formatting rule that calls the function directly.
• Nothing else — these are pure custom functions with no API keys or triggers.

The script

// IBANs are between 15 and 34 characters once spaces are removed.
const IBAN_MIN_LENGTH = 15;
const IBAN_MAX_LENGTH = 34;

// The default prefix every Northwind SKU starts with.
const SKU_PREFIX = 'NW-';

/**
 * Validates an IBAN using the ISO 7064 mod-97 checksum. Returns TRUE only
 * if the structure and the check digits both pass.
 *
 * @param {string} value The IBAN to check, spaces allowed.
 * @return {boolean|string} TRUE, FALSE, or '' for an empty cell.
 * @customfunction
 */
function VALID_IBAN(value) {
  // 1. Empty cells are not errors — return blank so the column stays clean.
  if (!value) return '';

  // 2. Normalise: drop spaces and upper-case the letters.
  const iban = String(value).replace(/\s/g, '').toUpperCase();

  // 3. Structural check — two letters, two digits, then the account part,
  //    within the allowed length range.
  if (
    !/^[A-Z]{2}\d{2}[A-Z0-9]+$/.test(iban) ||
    iban.length < IBAN_MIN_LENGTH ||
    iban.length > IBAN_MAX_LENGTH
  ) {
    return false;
  }

  // 4. Move the first four characters to the end, as the standard requires.
  const reordered = iban.slice(4) + iban.slice(0, 4);

  // 5. Replace each letter with its number (A=10 ... Z=35).
  const numeric = reordered.replace(/[A-Z]/g, (c) => c.charCodeAt(0) - 55);

  // 6. A valid IBAN leaves a remainder of exactly 1.
  return mod97(numeric) === 1;
}

/**
 * Validates a UK VAT registration number against the published formats:
 * the standard 9- and 12-digit numbers and the government/health-authority
 * variants. This is a format check, not a live HMRC lookup.
 *
 * @param {string} value The VAT number to check.
 * @return {boolean|string} TRUE, FALSE, or '' for an empty cell.
 * @customfunction
 */
function VALID_VAT_GB(value) {
  if (!value) return '';

  // Strip spaces and hyphens, then upper-case for a clean compare.
  const v = String(value).replace(/[\s-]/g, '').toUpperCase();

  // GB + 9 digits, GB + 12 digits (branch traders), or the GD/HA variants.
  return /^GB\d{9}$|^GB\d{12}$|^GBGD\d{3}$|^GBHA\d{3}$/.test(v);
}

/**
 * Validates an internal Northwind SKU: a fixed prefix followed by 3 to 12
 * upper-case letters or digits.
 *
 * @param {string} value The SKU to check.
 * @param {string} prefix The expected prefix; defaults to 'NW-'.
 * @return {boolean|string} TRUE, FALSE, or '' for an empty cell.
 * @customfunction
 */
function VALID_SKU(value, prefix = SKU_PREFIX) {
  if (!value) return '';

  // Build the pattern from the prefix so other product lines can reuse it.
  const re = new RegExp(`^${prefix}[A-Z0-9]{3,12}$`);
  return re.test(String(value).toUpperCase());
}

/**
 * Computes a long numeric string modulo 97, digit by digit, so the value
 * never overflows JavaScript's safe integer range.
 *
 * @param {string} numeric A string of digits.
 * @return {number} The remainder after dividing by 97.
 */
function mod97(numeric) {
  let remainder = 0;
  for (const ch of numeric) {
    remainder = (remainder * 10 + parseInt(ch, 10)) % 97;
  }
  return remainder;
}

How it works

1. Each function returns an empty string for an empty cell, so unfilled rows do not show up as errors.
2. VALID_IBAN normalises the input, runs a structural regex and length check, then performs the real ISO 7064 mod-97 checksum: it reorders the string, converts letters to numbers, and confirms the remainder is 1.
3. mod97 works through the numeric string one digit at a time. An IBAN can be 34 characters, which becomes a number far too large to hold directly, so the running remainder keeps it small at every step.
4. VALID_VAT_GB strips separators and matches the input against the four published UK VAT formats. It checks the shape only — it does not contact HMRC.
5. VALID_SKU builds its pattern from the prefix argument, so the same function validates other product lines by passing a different prefix.

Example run

With these values in column C, =VALID_IBAN(C2) returns:

C (IBAN)	Result
GB29 NWBK 6016 1331 9268 19	TRUE
GB29 NWBK 6016 1331 9268 18	FALSE
DE89 3704 0044 0532 0130 00	TRUE

And in column D and E:

D (VAT)	=VALID_VAT_GB(D2)	E (SKU)	=VALID_SKU(E2)
GB 123 4567 89	TRUE	NW-DESK01	TRUE
GB12345	FALSE	DESK01	FALSE

Use it

Type the function into the cell next to each identifier:

=VALID_IBAN(C2)
=VALID_VAT_GB(D2)
=VALID_SKU(E2, "NW-")

Drag each formula down its column. To highlight bad rows instead of showing a column of TRUE/FALSE, add a conditional-formatting rule with a custom formula such as =NOT(VALID_IBAN($C2)) and a red fill.

Watch out for

• VALID_VAT_GB and VALID_SKU check format only. A VAT number can be perfectly formatted and still not belong to a real business — only an HMRC lookup confirms that.
• VALID_IBAN validates the checksum and structure, but it cannot tell you the account exists or belongs to the right vendor. It catches typos, not fraud.
• VALID_VAT_GB covers UK numbers only. EU VAT IDs use country-specific formats; add a separate function per country you trade with.
• Custom functions return blank for blank input here. If you would rather flag empty cells, change the early return '' to return false.
• Conditional-formatting rules that call a custom function re-run on every edit. On a very large sheet that can feel slow — validate into a helper column once instead.