Among the multiple functions that can be achieved through the coding of macros in Excel, there is the ability to return all matches of a text as long as they are set within the parameters.
The ” Execute ” operation is the expression used to return all pattern matches that are within the search string created in Visual Basic.
Thanks to Visual Basic you can facilitate your time to work in Excel since through this program you can create macros and custom functions. With the same macros, you can import file names or copy data from one sheet to another.
What is the function of the regular expression pattern that will take care of returning all the matches of a text?
The first step is to create the regular expression object as a function called “Matches” in which, through parameters, a value will be found in the cell in which the person has written.
The objective through a macro code, returns a message with the matches found in the text separated by a disjunction.
Coding to create the pattern of the function
It has been clearly established that the regular expression pattern that will be created next is a function that will be in charge of validating texts entered in the sheet.
Access the ” Developer ” tab and click on ” Visual Basic ” to create a first ” Module ” that will contain the code of the function.
You will start by setting the start of the function, placing the name of matches and within the parentheses, you must place the value parameters of the text entered in the cell as a string.
Public Function matches (ByVal cell as String)
Once this is done, you can start writing the regular expression object that will establish the parsing pattern, giving it whatever name (SS) you want.
Set SS = CreateObject (“VBScript.RegExp”)
Start a variable that you will name as text to establish all the matches that will be found when executing the macro, which will be of type string and will be started at 0.
Dim text as String
Text = “”
Then it establishes the properties of the regular expression, where all will be placed as true and the pattern will be the letters from a to z accompanied by the plus symbol ” + ” to also analyze any other text within the string.
With SS
.Global = True
.Multiline = True
.IgnoreCase = True
.Pattern = ”[az] +”
End With
If there is a match within the text, it must be extracted using execute, or else it will display a message stating that nothing was found.
In the case of finding content, you must set a variable that you will name as found to place the value of the matching cell.
It should be noted that each match found will be traversed with an auxiliary variable determined in this example as ” x ” in a ” For each ” structure.
If SS.test (cell) Then
Set found = SS.Execute (cell)
For Each x In found
Aux = x
If text = ”” Then
Text = aux
Else
Text = text + ”| “+ Aux
End If
Next x
Finally, it assigns that the value of the regular expression object is the text that will be entered in the cell or sets the message if no matches are found, in order to end the function.
Matches = text
Else
Matches = ”No matches found”
End If
End Function
Result code
To return all the matches of a text, a new ” Module ” is created to establish a code that will show the result of the analysis of the cell’s content, working according to the pattern created previously.
To this will be added two variables called value and string of type string to which the values of the function will be assigned.
Sub matches ()
Dim value, string as String
Value = ActiveCell.Value
String = matches (value)
MsgBox string
End Sub
In this way, the function and the macro have been completed, which you can test by adding content to a cell with different symbols and accessing the ” Macros ” button to press ” Execute ” on the function.
It will conclude by appearing a window with the matches that are within the established parameters, separated from each other by the disjunction symbol.
If you work with this program you may at some point present an error, but these are easy to solve.