Thanks to Visual Basic you can facilitate your time working in Excel, since through this program you can create custom macros and functions. With the same macros you can import filenames or copy data from one sheet to another.
What is the function of the regular expression pattern that will take care of returning all matches of a text?
The first step is create the regular expression object as a function called "Match" where, via parameters, a value will be found in the cell where the person wrote.
The target 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
Clearly it has been established that the regular expression pattern that will be created later is a function that will take care of validating the texts inserted in the sheet.
Access the details » Developer »And click on» Visual Basic "To create a first" Module »Which will contain the function code.
You will start by setting the start of the function, by entering the name of the matches and in brackets you have to insert as string the value parameters of the text inserted in the cell.
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 the name (SS) you want.
Imposta SS = CreaOggetto («VBScript.RegExp»)
Start a variable that you will call as text to establish all matches to come found while running the macro, which will be of type string and will start from 0.
Dimmed text as a string
test = ""
It then establishes the properties of the regular expression, where everything will be placed as true and the model will be the letters a to z accompanied by the plus symbol » + »To also parse any other text within the string.
Con SS
.Global = True
.Multiline = True
.IgnoreCase = True
.Modello = «[az] +»
Ends with
If there is a match within the text, it must be extracted using execute, otherwise it will display a message indicating that nothing was found.
In case of content search, you need to set a variable that will call as found to insert the corresponding cell value.
It should be noted that each match found will be traversed with an auxiliary variable determined in this example as " x »In 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 = auxiliary
Other
Text = text + ”| «+ Aux
End if
Forward x
Finally, it assigns the value of the regular expression object to be the text that will be inserted into the cell or sets the message if no matches are found, to terminate the function.
Matches = text
Other
Matches = "No matches found"
End if
End of function
Result code
To return all coincidences of a text, a new one is created " Module »To establish a code that will show the result of the analysis of the cell contents, working according to the previously created scheme.
To this two variables named value and string of type string will be added to which the function values will be assigned.
Secondary matches ()
Dim value, string as String
Value = ActiveCell.Value
String = matches (value)
Stringa MsgBox
End subtitle
This completes the function and the macro, which you can test by adding content to a cell with different symbols and accessing the " Macro "To press" Run ”On the function.
It will end with the appearance of a window with the correspondences that fall within the established parameters, separated from each other by the disjunction symbol.
If you work with this program you may have an error at some point, but these are easy to fix.
