Among the many functions that can be achieved through coding macros in Excel, there is the ability to repay all matches text as long as these are established within the parameters.
The operation « Execute » it is the term used to return all pattern matches that are within the search string created in Visual Basic .
You may also be interested in:
Thanks to Visual Basic can ease your time to work on Excel, because through this program can create macros and custom functions. With these macros can import the names of the files or copy data from one sheet to another.
What is the function of regular expression pattern that will handle return all matches of a text?
The first step is create the regular expression object as a function called «Matches» where by parameters you will find a value in the cell which you enter the person.
The goal through a macro code, this returns us a message matches found in the text separated by a disjunction.
Encoding to create the pattern function
Clearly it has been established that the regular expression pattern that will be created next is a function that is responsible for validating text entries on the sheet.
Go to the tab « Developer » and clicking « Visual Basic » to create a first « Block » containing the code of.
You’ll start by setting the start of the function, placing the name matches and within the parentheses must place the value parameters of the text entered in the cell as a string.
Public Function matches (ByVal cell as String)
Once done, you can start writing in order to establish regular expression pattern analysis, placing the name (SS) you want.
set SS = CreateObject ( «VBScript.RegExp»)
Start a variable denominarás as text to set all matches will be found to the run the macro , which will be of type string and will be initiated at 0.
text as String Dim
Text = «»
then set the properties of the regular expression, where all are placed as true and pattern will be the letters a through z accompanied by the addition symbol « + «to also analyze any other text within the chain.
.Global = True
.Multiline = True
.IgnoreCase = True
.Pattern = «[a-z] +»
If the text within a match occurs, must be removed using EXECUTE , or otherwise display a message indicating that nothing has been found.
In the case of finding content, you must set a variable that denominarás as found to place the value of the cell match.
Note that each match found will travel with an auxiliary variable determined in this example as « x » a structure « For each «.
If SS.test (cell) then
set found = SS.Execute (cell)
For Each x In found
Aux = x
If text = «» Then
Text = aux
Text = Text + «| «+ Aux
Finally, assign the value of the object of the regular expression is the text that will be entered into the cell or sets the message if no matches are found, in order to end the function.
Matches = text
Matches = «No matches found»
To return all occurrences of a text creates a new « Block » to establish a code that will display the result of the analysis of the contents of the cell, working according to the pattern previously created.
To this is added two variables called value chain and string type to which will be assigned the function values.
Under matches ()
Dim value chain as String
Value = ActiveCell.Value
= string matches (value)
Thus it is completed the function and macro, which you can try adding content to a cell of different symbols and accessing the button « Macros » to press « Run «on the role.
Conclude popping up a window with the matches that are within established parameters, separated by the symbol of disjunction.
If you work with this program may at some point you present an error , but these are easy to fix.
I'm sorry this content was not useful for you!
Let me improve this content!
Tell me, how can I improve this content?
You may also be interested in: