收录日期:2020/12/05 03:40:01 时间:2010-09-07 20:48:17 标签:regex,excel,vba,user-defined-functions,worksheet-function

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.

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.