Why does the following pattern work with one of my udf vs the other


First, I'd like to give credit to the post answer that helped me greatly, here


Another post that helped me was here on StackOverflow.



The StackOverflow UDF works with the following pattern "12abc" with "^[0-9]{1,3}]".


Now, the post answer does not work with this particular pattern and example...


Any thoughts?





3 Replies


Please attach a sample workbook with the code and some dummy data, so that we don't have to do all the work.

Good point, added to OP.



The getTargetValueV2 function tries to return the first of the submatches of the last match.

A submatch is a value corresponding to an expression between parentheses in the pattern.


The formula in B1 uses "target1 = (\d+)" as pattern. It has one expression in parentheses: (\d+).

The matches collection contains two items: "target1 = 1234" and "target1 = 5678". The numeric strings 1234 and 5678 are the result of the expression (\d+), so they are the submatches.


The formula in B2 uses ^[0-9]{1,3} as pattern. This does not have an expression in parentheses, so there will be no submatches.

You could change the pattern to ^([0-9]{1,3}) so that the formula becomes




 The formula will then return 12.