Verifying Tax IDs With REGEX

[robertlenius]

Have you ever found yourself putting data into some online application only to have it fire back that the format is incorrect? This could be things like a phone number, address, or in the bad example on the right, a name 😉.

Typically, this is done through regular expressions, also known as REGEX functions. These functions can do a wide variety of things, one of which is verifying if data is in the expected format.

But how is this useful for Tax? Well, it could be the case that you need to screen through your data on a regular basis to ensure the quality of it for compliance purposes. One such example could be with vendor / customer Tax IDs, seeing as each country has their own individual formats it’s important to check if the Tax ID in your data set is in a valid format for the country in question. A comprehensive list of these formats can be found here.

Through Alteryx we can use the built-in REGEX tool to build verification checks into a workflow to ensure that Tax IDs are in the proper format. Let’s use Bulgaria’s format for example. Per the OECD guidelines their format is:

“For entities: 999999999 – 9 digits”

This can be translated into the regex function:  [0-9]{9}

Breaking this down, it means that the number must be nine characters long and these characters can be from zero to nine.

On the right is the configuration pane for this REGEX function, you can see we're setting the output method to “Match” which will verify if the regular expression is true or false.

This configuration provides the output to the left, we can see that the first tax ID has a valid format and is marked as TRUE.  The second tax ID contains a letter, leaving the REGEX to mark it as FALSE.

While this might seem straightforward, other countries have more complexity in their TAX ID’s and subsequently would require more complex REGEX functions to achieve the desired output. A comprehensive list of these functions could be built into an Alteryx macro to automatically detect the country and then run the proper check. This logic could also be applied to many data sets outside of tax if you need to verify that data is in a specific format.

Disclaimer: This example is for educational purposes only and not intended to provide advice on tax compliance topics. This blog will not be updated should applicable regulations be changed.