Home › Guides

Guide

How to validate ABNs in Google Sheets

A quick way to catch typos in a column of Australian Business Numbers, then confirm the real ones against the Australian Business Register.

An ABN that is one digit out can send a payment to the wrong business, put the wrong number on a tax invoice, or leave you chasing a supplier for weeks. Most of those mistakes are simple typing errors, and the ABN format is designed so you can catch them before anything leaves your spreadsheet.

This guide covers two levels of checking. The first is the checksum, which you can run in Google Sheets for free with a single formula. The second is a register lookup, which tells you whether the ABN actually exists and who it belongs to.

How the ABN checksum works

Every ABN has 11 digits, and the first two digits are check digits. The Australian Taxation Office publishes the rule, so you can test a number yourself before you look it up:

1. Subtract 1 from the first digit.
2. Multiply each of the 11 digits by its weight: 10, 1, 3, 5, 7, 9, 11, 13, 15, 17, 19.
3. Add the 11 results together.
4. If the total divides evenly by 89, the number passes the checksum.

Take 51 824 753 556. After subtracting 1 from the first digit, the digits are 4 1 8 2 4 7 5 3 5 5 6. The weighted products are 40, 1, 24, 10, 28, 63, 55, 39, 75, 85 and 114, which add up to 534. 534 is 89 × 6, so the number passes. Change the last digit to 7 and the total becomes 553, which leaves a remainder of 19, so it fails.

A pass only tells you the number is well formed. It does not tell you the ABN was ever issued, who holds it, or whether it is still active. For that you need the Australian Business Register (ABR).

Free method: a checksum formula in Google Sheets

You can do the whole calculation in one cell. Put your ABNs in column A, starting at A2, then paste this into B2 and fill it down:

=LET(d, REGEXREPLACE(TO_TEXT(A2), "[^0-9]", ""),
  IF(LEN(d) <> 11, FALSE,
    MOD(SUMPRODUCT((VALUE(MID(d, SEQUENCE(11), 1)) - (SEQUENCE(11) = 1))
      * {10;1;3;5;7;9;11;13;15;17;19}), 89) = 0))

Here is what each part does:

• REGEXREPLACE strips spaces, hyphens and anything else that is not a digit, so “51 824 753 556” and “51824753556” are treated the same.
• LEN(d) <> 11 returns FALSE straight away if there are not exactly 11 digits.
• MID with SEQUENCE(11) splits the number into its 11 digits, and (SEQUENCE(11) = 1) subtracts 1 from the first one only.
• SUMPRODUCT multiplies each digit by its weight and adds the results. MOD(…, 89) = 0 is the pass test.

Two practical tips. Format the ABN column as plain text (Format → Number → Plain text) before you paste, so Google Sheets keeps the number exactly as typed instead of reformatting it. And add a filter on column B, so you can show only the FALSE rows and fix them first.

Free method: confirm the ABN on the register

A number that passes the checksum still needs to be confirmed. The free ABN Lookup website at abr.business.gov.au lets you search one ABN at a time. It shows the ABN status (Active or Cancelled, with the date), the entity name, GST registration and the state and postcode of the main business location. For a handful of numbers this is all you need. For a long list it becomes slow, because each number is a separate search and you copy the results back by hand.

How ABN Formula does it

ABN Formula for Google Sheets™ adds both checks as formulas:

=ABN_VALID(A2)    TRUE or FALSE, from the checksum only
=ABN_STATUS(A2)   Active or Cancelled, from the ABR
=ABN_NAME(A2)     registered entity name, from the ABR

1. Open ABN Formula, then Open sidebar, once in the spreadsheet. This authorises the add-on to look numbers up for you.
2. Use =ABN_VALID(A2) to flag typos. It runs the same checksum as the formula above and never contacts the register, so it does not use your monthly allowance.
3. Use =ABN_STATUS(A2) or =ABN_NAME(A2) on the numbers that pass. Both read from the ABR through the free ABN Lookup web services.

Each formula also accepts a range, such as =ABN_VALID(A2:A200), and fills the results down. Numbers that fail the checksum come back with a message asking you to check for a typo, and they are not sent to the register.

Register answers are saved for up to 24 hours, so a result can be up to a day old. If you are about to make a payment decision, check the ABR directly as well.

Related guides

• Bulk ABN lookup in a spreadsheet
• Check GST registration for a list of suppliers
• Check ABNs are active before paying invoices

ABN Formula is not affiliated with, endorsed by, or part of the Australian Government, the Australian Taxation Office or the Australian Business Register. ABN status, entity names and GST registration come from the ABR through the free ABN Lookup web services. This guide is general information, not tax advice.