If TRUE or omitted (default), case-sensitive matching is performed if FALSE - case-insensitive.
Match_case (optional) - defines the match type. When placed directly in a formula, a pattern must be enclosed in double quotes. Pattern(required) - the regular expression to match. Can be supplied as a cell or range reference. Text(required) - one or more strings to search in. If you are not very experienced with VBA, this guide may be helpful: How to insert VBA code in Excel. Paste the code in the VBA editor, and your new RegExpMatch function is ready for use. ReDim arRes(1 To cntInputRows, 1 To cntInputCols)ĪrRes(iInputCurRow, iInputCurCol) = regex.Test(input_range.Cells(iInputCurRow, iInputCurCol).Value) Set regex = CreateObject("VBScript.RegExp") Public Function RegExpMatch(input_range As Range, pattern As String, Optional match_case As Boolean = True) As Variantĭim arRes() As Variant 'array to store the resultsĭim iInputCurRow, iInputCurCol, cntInputRows, cntInputCols As Long 'index of the current row in the source range, index of the current column in the source range, count of rows, count of columns Luckily, Excel's VBA has an inbuilt RegExp object, which you can use in your code like shown below: Regular expression matching using Regex ToolsĮxcel VBA Regex function to match stringsĪs it's pretty clear from the heading, in order to use regular expressions in Excel, you need to create your own function. But Excel does not support regexes! No worries, we'll force it to :) And how do you know if a cell contains information that matches a given pattern? Obviously, by using regular expressions. When looking for a specific string in a cell, the FIND and SEARCH functions come in handy. 
When you need to find a certain value in a range of cells, you'd use the MATCH function. In this tutorial, we'll have an in-depth look at how to use regex to match strings in Excel.
0 kommentar(er)
