How I Use Excel’s Regex Functions to Power Up My Search Game
February 9, 2025

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

Fast links

Brief content

  • The regime patterns help effectively perform the search for text (for example, emails, numbers, specific sequences) using characters.

  • Excel provides predetermined functions of Reghex (Reghextest, RegexExtract, RegexReplace) for searching, extracting and replacing data.

  • Combine the functions of the mode with other EXCEL functions for more advanced manipulations with data and validation.

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.

Regular expressions are not unique for Excel-on, available in several text editors, programming languages, command string tools, IDES and even the competitor of Excel, Google Sheets.

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:

Symbol

Description

Determines the range of characters in brackets.

^

Compare the beginning of the line.

$

Compare the end of the line.

Field

Compare any character except for a new line character.

*

Compare 0 or more of the previous character.

+

Compare 1 or more previous characters.

()

Group suitable characters in one.

[]

Compare any of the characters inside the brackets.

[^]

Compare any character that is not inside the brackets.

{n}

Combine exactly n copies of the previous character.

{n,}

Correspond to n or more entries of the previous character

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

Sample mode

Description

[0-9]

Compare one digit from 0 to 9

[a-zA-z0-9]

This is a combined range that corresponds to one symbol from the lower register from A to Z, a common deposit from A to Z and from 0 to 9.

^Pro

Correspond to any line that begins with profileField

[^$]

Correspond to any character who is not $Field

(Con)

Group the template air conditionerField

a {3,}

Compare 3 or more inputs of the latter A (For example, AIN aaor AAA)

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.

Regextest function in Excel.

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

REGEXEXTRACT function in Excel.

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

The function of the mode in Excel.

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.

REGEXTest Association and if functioning 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.

Source link

Leave a Reply

Your email address will not be published. Required fields are marked *