RegexMatch, RegexReplace

Formula: RegexMatch() and RegexReplace()

Category: String

RegexMatch() and RegexReplace() share two parameters:

RegexReplace() takes one additional parameter:

⁠

The first time I read the documentation on Regex I said: “What the f**k is this?” and quickly left the page. Go ahead, look at it and tell me you’re not confused → [

Regex Documentation⁠

]

Despite it being scary to those unacquainted, Regex is by far one of the most useful formulas you can have in your tool-belt and there are a few powerful expressions you can learn that will help you with your Coda data.

Regular Expression or (Regex) is one of the most powerful, flexible, and efficient text processing approaches. Regex has its own terminologies, conditions, and syntax; it is, in a sense, a mini programming language. Regex can be used to add, remove, isolate, and manipulate all kinds of text and data [

source⁠

].

In short, Regex is a way of reliably locating patterns in text. You use short hand “code” to match patterns of text within a longer string. For example:

One could, and some have,

written entire courses⁠

on Regex. So rather than teach you Regex from the ground-up, the Coda School is going to help you master a couple patterns that will make Regex a useful and powerful formula for your workflows. We will start with some patterns you need to know followed by a couple of example use-cases with those patterns.

Click into the view button below to learn more about the pattern or follow along below to see how they are used in combination to do some magical things

Below is a list of addresses that a business owner, Freddy, has on file for customers. In order to better analyze the addresses and answer questions like “In what state do a majority of my customers live?” - Freddy uses Regex formulas in the “Address Line Only” and “State” columns.

Expand the sections below to see how Regex is used and an explanation of the patterns used to draw out this information.

What pattern(s) did this solution use? Select any that apply.

⁠

1

⁠

Answer

This solution used pattern 1.

What pattern(s) did this solution use? Select any that apply.

⁠

1

⁠

1

⁠

Answer

This solution used pattern 1, 3, and 5.

Other things to note

This solution used a hidden formula called RegexExtract().

When you extract text from a string, the Regex Robot extracts everything you tell him to. In this case, you asked him to extract a space, two capital letters, and another space. Therefore the end result would have had extra spaces in it. Hence the Trim(), which then allowed us to return just the state value itself.

The

following table

⁠

is a list of survey responses a business owner has collected. The owner wants to answer two questions with her data and uses Regex to answer them in the gray columns below.

Did each responder provide a valid email address?

Which responders left information about either our prices or our service?

Expand the sections below to see how Regex is used and an explanation of the patterns used to draw out this information.

What pattern(s) did this solution use? Select any that apply.

⁠

Answer

This solution used pattern 1, 6 and 7.

Other things to note

Email validation is another beast. While the above Regex Expression will allow you to validate most emails, email validation is notoriously difficult to do.

From Google, here’s another email validation regex string you can use: ^[\w-+.]+@[\w-]+\.[\w-.]+$

Simply googling “regex expression to match _______” can give you tons of ideas and examples!

What pattern(s) did this solution use? Select any that apply.

⁠

Answer

This solution used pattern 8.

Other things to note

You can use a whole host of other formulas in this case to check this, but the | operator in regex allows you to make a much simpler formula when you are checking for multiple conditions in text.

Regex is case sensitive, so you will notice that we used Lower() to first ensure that our regex expression would catch any price/service in the text no matter how it was capitalized.

⁠

RegexMatch, RegexReplace

⁠
⁠
⁠

What is Regex?

How to use Regex in Your Docs

Need to Know Patterns (Aka - Regex Expressions)

Regex with Addresses

Address Line Only

State

Regex with Larger Text

Valid Email Address

Price Service

RegexMatch, RegexReplace

⁠⁠⁠

What is Regex?

How to use Regex in Your Docs

Need to Know Patterns (Aka - Regex Expressions)

Regex with Addresses

Address Line Only

State

Regex with Larger Text

Valid Email Address

Price Service

⁠
⁠
⁠