How to return all matches of text entered with regex template in Excel
Among the multiple functions that can be obtained by coding macros in Excel, there is the possibility of returning all text matches as long as they are defined in the settings.
The operation " Execute Is the expression used to return all pattern matches that are in the search string created in Visual Basic .
Thanks to Visual Basic, you can make your working time easier in Excel, because thanks to this program you can create macros and custom functions. With the same macros, you can import file names ou copy data from one sheet to another.
What is the function of the regular expression pattern which will take care of returning all the matches of a text?
The first step is to create regular expression object as a function called Correspondances in which, through parameters, a value will be found in the cell in which the person wrote.
The objective through a macro code, this returns a message with the matches found in the text separated by a disjunction.
Coding to create the function model
It has been clearly established that the regular expression model that will be created next is a function that will take care of validating the texts entered in the sheet.
Go to the »tab Junior Backend Developer (Intern) "And click" Visual Basic "To create a first" Modules Which will contain the function code.
You will start by defining the start of the function, placing the name of the matches and in parentheses, you need to place the value parameters of the text entered into the cell as a string.
Public function matches (ByVal cell as a string)
Once that's 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 running the macro , which will be of type string and will start at 0.
Dim text as a string
Text = ""
Then it establishes the properties of the regular expression, where everything will be placed as true and the model will be the letters from a to z accompanied by the plus symbol » + To parse any other text in the string as well.
With SS
.Global = True
.Multiline = True
.IgnoreCase = True
.Pattern = "[az] +"
End with
If there is a match in the text, it should be extracted using execute , otherwise it will display a message indicating that nothing was found.
In the case of content search, you must define a variable that you will name as found to place the value of the corresponding cell.
Note that each match found will be searched with an auxiliary variable determined in this example as " x " in structure » For each «.
If SS.test (cell) Then
Set found = SS.Execute (cell)
For each x in found
Aux = x
If text = "" Then
Text = to
Other
Text = text + ”| "+ To
End yes
Next x
Finally, it assigns that the value of the regular expression object is the text that will be entered into the cell or sets the message if no match is found, in order to terminate the function.
Matches = text
Other
Matches = "No matches found"
End yes
End function
Result code
To return all the coincidences of a text, a new one » Modules »Is created for establish a code that will display the result of the analysis of the contents of the cell, working according to the model created previously.
To that, two variables called value and string of type string will be added to which the values of the function will be assigned.
Sub-matches ()
Dim value, string as a string
Value = ActiveCell.Value
String = match (value)
MsgBox string
End Sub
This way the function and the macro are done, which you can test by adding content to a cell with different symbols and going to the button » Macros "To press" Execute On the function.
It will end by showing a window with the matches that are in the established parameters, separated from each other by the disjunction symbol.
If you work with this program, you can at some point present an error , but these are easy to resolve.
