How to Use REGEXMATCH Function in Google Sheets?

How to Use REGEXMATCH Function in Google Sheets?

Searching within tables is a common task when working with Google Sheets. With the REGEXMATCH function, we can easily find the answer to the question of whether there is an exact match for the word we are looking for in a table.

We can easily search with 'Find and replace'. You can find detailed information in this article: "How to search in google sheets?"

Here REGEXMATCH is divided as follows. We can search for a match under a condition such as starting with x, continuing with y, ending with z. In return, it returns TRUE or FALSE.

The syntax is a bit messy depending on the condition we want to search for. But you don't have to worry. Below I will write the expressions you will use while searching.

What is the REGEXMATCH function syntax like?

When we type the words or letters we want to search with 'Find and replace', we can reach the results we want. But if we don't know the exact word we want to search for, we may want to search for similar ones. In such cases, we use some characters for the condition.

Metacharacter

Definition

^

What did the cell start with

$

What did the cell end with

|

The 'or' operator

[]

It means it contains letters from a to z

[a-z]

It means it contains letters from A to Z

[A-Z]

It means it contains numbers from 0 to 9

+

Repetition of a character

.

Refers to a single character

Let's examine with examples.

Does it start with “filter”?

=REGEXMATCH(A1,”^filter|Filter”) We have to write both as it is case-sensitive. “|” or means.

If we are questioning that something does not start with "x", we should use "^".

Does it end with “Dashboard”?

=REGEXMATCH(A1,” Dashboard$”)

If we are querying the end of the cell with "x", we should put the "$" character at the end.

Does it end with “he” or “she”?

=REGEXMATCH(A1,” he|she$”)

Does cell A1 contain numbers?

=REGEXMATCH(A1,”[0-9]”)

We can also find out if there is a number in the cell by using “\d”.

Do the cells contain e-mail addresses?

We can learn this easily. Since e-mail addresses contain "@", we just need to put "@" inside the function.

We can check whether a cell starts with "x" and ends with "y".

=REGEXMATCH(A1,"^How[a-zA-Z0-9 ]+Dashboard$")

With this function, we can search. Let's explain one by one.

A1 → Will be the cell I want to query

^How→ Does it start with How? Queries what the ^ character starts with

[a-zA-Z]→ Covers upper and lower case letters used between start and end

[a-zA-Z0-9 ]→In addition to uppercase and lowercase words, the sentence can also contain numbers. So I added [0-9 ] to broaden the search. It should be noted that there is a space after 9. Because there will be space between words.

Dashboard$ '$' I question whether it ends with 'Dashboard'