How to return all matches of a text entered with a regular expression pattern in Excel

0
78
How to return all matches of a text entered with a regular expression pattern in Excel

Among the many functions that can be achieved through the coding of macros in Excel, there is the ability to return all the matches of a text as long as they are established 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 make your time working in Excel easier, because 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 be in charge 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, by means of parameters, a value will be found in the cell in which the person has written.

The objective through a macro code, this returns us a message with the matches found in the text separated by a disjunction.

Coding to create the pattern of the function

It has clearly been 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 function code.

You will start by setting the start of the function, naming it matches and inside the parentheses you should put the value parameters of the text entered in the cell as a string.

Public Function matches(ByVal cell as String)

magnifying glass on top of bars

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 initialized at 0.

Dim text as String

Text=“”

Next, set the properties of the regular expression, where they will all be set to true and the pattern will be the letters from a to z accompanied by the addition symbol “+” to also analyze any other text within the string.

With SS

.Global=True

.Multiline=True

.IgnoreCase=True

.Pattern=”[a-z]+”

End With

If there is a match within the text, it must be extracted using execute, or else it will display a message indicating that nothing was found.

In the case of finding content, a variable must be established that you will name as found to place the value of the matching cell.

It should be noted that each match found will be iterated 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

Lastly, set the value of the regular expression object to be the text that will be entered into the cell or set the message if no matches are found, to end the function.

green excel icon

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 content of the cell, 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.

submatch()

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 of different symbols and accessing the “Macros” button to press “Run” on the function.

It will conclude with a window appearing 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 get an error at some point, but these are easy to solve.

Previous articleHow to consolidate the values ​​or data of a row in a spreadsheet in Excel
Next articleHow to extract the text from an image and transfer it to Word without free programs?

LEAVE A REPLY

Please enter your comment!
Please enter your name here