How To Return All Occurrences Of An Entered Text With A Regular Expression Pattern In Excel


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 .

How to return all occurrences of a text entered with Pattern Expression Regular in Excel

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)

 magnifying glass over top bar

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.

With SS

.Global = True

.Multiline = True

.IgnoreCase = True

.Pattern = «[a-z] +»

End With

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

Else

Text = Text + «| «+ Aux

End If

Next x

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.

 icon green excel

Matches = text

Else

Matches = «No matches found»

End If

End Function

Result Code

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)

MsgBox chain

End Sub

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.

How useful did you find this content?

Click on a star to rate!

Average score 0 / 5. Counting of votes: 0

So far, no votes. Be the first to rate this content.

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:

Deja un comentario