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.

Lance Roberts · Accepted Answer · 2010-09-07 20:47:53Z

2

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.

answered Sep 7, 2010 at 20:47

Lance Roberts

22.9k32 gold badges115 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rtribaldos Over a year ago

I how the UDF should be? I dont know VBA

Lance Roberts Over a year ago

@sword, You have to create a UDF in VBA, it's not hard to do and there are lots of pages you can google to explain it. Here's one page that goes over some basics, and another covers some Regex functions specifically.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related