An Excel VBA Custom function (UFD) that can be tried multiple times to match and replace with more than one regular expression

The following code can use multiple regular expressions to make multiple match attempts on the target cell, such as a successful match, stop trying to match the other regular expression, and replace with the corresponding substitution expression for the regular expression, returning the replacement result.

Need to do early Binding before use. In the VBE editor, select Tool-reference in the menu bar,

After the dialog box pops up, select Microsoft Vbsscript Regular Expression 5.5, tick the hook and click OK.

This UDF is used in the following ways:

Text parameter: The original text or cell that needs to be processed.

Matchpatternrange parameter: The range of cells in which the regular expression group resides. Place more than one regular expression in a row or column in a table, passing in as the Matchpatternrange parameter.

ReplacePattern parameter: The range of cells where the replacement string character corresponds to each regular expression. Place the text you used to replace in the row or column next to the regular expression, and pass in as the Replacepatternrange parameter.

IgnoreCase parameter: Whether case is ignored when looking, the default value is true, that is, the case difference is ignored.

FunctionRangeregexreplace (ByValText as String,ByValMatchpatternrange asRange,ByValReplacepatternrange asRange,Optional ByValIngorecase as Boolean=True) as String'by Jing He 2017-9-1DimI as IntegerJ as IntegerX as IntegerDimPattern () as String,Replace() as StringReDimPattern0  toMatchpatternrange.count-1) as StringReDim Replace(0  toReplacepatternrange.count-1) as StringI=0 for  eachCinchMatchpatternrange pattern (i)=C.value i= i +1NextCJ=0 for  eachCinchReplacepatternrangeReplace(j) =C.value J= j +1NextCIfI <> J ThenRangeregexreplace="Numbers of cells in Matchpatternrange and replacepatternrange is not equal."End IfDimRegex as NewRegExp withregex. Global=True    . MultiLine=True    . IgnoreCase=IngorecaseEnd  withRangeregexreplace="-" forx =0  toI1 Step 1Regex.pattern=pattern (x)IfRegex. Test (Text) ThenRangeregexreplace= Regex.Replace (Text,Replace(x))End IfNextxEnd Function

Incidentally, with a custom function that uses a single regular expression to find and replace, the regular expression can be written directly into the arguments of the formula, without having to be placed inside the cell and then referenced.

FunctionRegexreplace (ByValText as String,ByValMatchpattern as String,ByValReplacePattern as String,Optional ByValIngorecase as Boolean=True) as String'by Jing He 2017-9-1DimRegex as NewRegExp withregex. Global=True    . MultiLine=True    . IgnoreCase=ingorecase. Pattern=MatchpatternEnd  withIfRegex. Test (Text) ThenRegexreplace=Regex.Replace (Text, ReplacePattern)ElseRegexreplace="-"End IfEnd Function

An Excel VBA Custom function (UFD) that can be tried multiple times to match and replace with more than one regular expression