How I Use Excel’s Regex Functions to Power Up My Search Game

Filtering and search in Excel can feel like a guess game. RegEX functions change this. Now I can accurately determine exactly what I need – patterns, partial coincidences or extraction of structured data – without inciting sweat.

What is region?

REGEX is a type of template used to search for a text line or a sequence of characters for coincidence. Have you thought how web sits can tell you that the e -mail template that you entered on the entrance to the system is invalid? This is one examples of the regulation template using the signature by e -mail at work.

Rex may seem difficult, and if you want to use it fully, but you do not need to be a programmer to effectively use it. In some cases, you can get away with it, just knowing how to use several characters and main templates. I will hold this leadership as easier as possible so that you can start using them.

Here are the symbols that we will use in this guide:

Simple regime templates that you can build using these characters include:

RegEX functions are predetermined Excel formulas that can be used to determine the template to search and manipulate text lines. At the moment, there are three functions of the regime, and we will consider how to use them separately and with other functions.

Search for templates

The first function we are going to look at is regextest. A text line is required that you want to use to search and regulation template, and uses the latter to find a coincidence in the first. The function will return either True or LIEField

The syntax for the regextest function is as follows:

        REGEXTEST(string_to_search, regex_pattern_to_use, [case_senstivity])

    

The first two parameters, string_to_search And regex_pattern_to_useare self -explosive. A [case_sensitivity] The parameter is not mandatory-all placed in square brackets, when the conversation about Excel syntax is not mandatory, and you want to make the search sensitive to cases (0) or the case is insensitive (1) By default, sensitive to cases.

In our example, we will use Reghextest to see whether the user has introduced the actual email address using the following formula:

        REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$")

    

Here we are looking for in the cell B3 To see if it contains an email address using the above mode template:

        ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$
    

If I close the formula in the cell C3 And enter John.Doe@Example.com in Cell B3He will return True Because it corresponds to email signatures.

Next, let’s look at the Rexexextract function. He returns tuning (part of the line), which corresponds to the regulation provided by the picture.

The syntax for the Rexexextract function is as follows:

        REGEXEXTRACT(string_to_search, regex_pattern_to_use, [return_mode], [case_senstivity])

    

Continuing an example of e -mail, let’s add a formula to the cell B4 To extract the name of the user of part of the email.

This is what the formula will look like:

        =REGEXEXTRACT(B3, "([^@]+)")

    

In this formula, we extract everything before @ symbol in the email address entered into B3Field

Find and replace using regions

The final function of the regular position, on which we will consider, is regexreplace. This function is similar to the Excel replacement function, but also supports the corporation. A text line is required that you want to change, and checks if there is an adjustment corresponding to a specific regulation template. If he finds him, he replaces it with the spare line provided.

The syntax of the recent function is as follows:

        REGEXREPLACE(string_to_modify, regex_pattern_to_use, replacement_string, [number_of_occurrences], [case_senstivity])

    

Here are the important parameters to which pay attention to this function:

• String_to_Modify: The text line you want to change.
• Replacement_: Line to replace tuning.
• number_of_occurrences: The exact copy that you want to replace.

Here is an example of using the function to replace the part of the user’s name of the email by another text line:

        =REGEXREPLACE(B3, "^[^@]+", "jane.doe")

    

Value B3 is JOHN.DOE@EXAMple.comAnd after we enter the formula higher in the cell C3He will return Jane.doe@example.comField

Combine regulation with other functions

You can also combine the functions of the mode with other functions in Excel. For example, you can combine the REGEXTest function with the Excel if operator, and display the corresponding message based on the result.

Here is an example of the formula:

        =IF(REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"), "This is a valid email address!", "The email address is invalid!")

    

This formula uses the IF operator to check if the email address entered into the B6 cell and then display is valid This is a valid email address! If so true or The email address is invalid! If so LIEThe field as an alternative, you can combine this with the search function to quickly find data in Excel.

This is not intended for extensive guidance on regular expressions – which will require several articles yourself. Nevertheless, this is a good way to start using Regex in Excel. Examples of use and opportunities are limited only by your imagination.