How to create a function in Excel to validate cells using RegExs

Question

I have a long spreadsheet with tariff codes that I need to validate and I would like to create a function with RegEx to do it automatically (this is a daily task that I will have to do for the following months and I would like to automatize)

For example in Column A, I have the following data:

CODE
1000.00.00
1000.10.00
1020.12.99
...

But some times, the codes are mispelled like (1020..23.99 or 1020.23.124), and I would like to make a validation in column B with a function like this in each cell:

=isvalid_function(A2,"^\d{3,4}\.\d{2}\.\d{2}$")

..and get TRUE or FALSE as result.

Answer 1

You need to add the reference to Microsoft VBScript Regular Expressions to Excel, then you can use Regex, see this link for some more detail. Then you'd create a UDF called isvalid_function that would implement that.