Formula: RegexMatch() and RegexReplace()
Category: String
RegexMatch()
Checks if text values match a “regular expression”
It’s kind of like saying...
Hey Coda, I have a bunch of text for you. Now look at this pattern...can you Identify this pattern in my text?
RegexReplace()
Replaces text via “regular expression”
Or put another way...
Hey Coda, I have a bunch of text. Now look at this pattern...can you Identify this pattern in my text and replace it with this? RegexMatch() and RegexReplace() share two parameters:
Text
RegexMatch(Text, Regular Expression)
This is the text you want to seach with your pattern.
RegularExpression
RegexMatch(Text, RegularExpression)
This is the pattern you're looking to search for within your text.
RegexReplace() takes one additional parameter:
ReplacementText
RegexReplace(Text, RegularExpression, Replacement Text)
This is the pattern you want to replace your text with.
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 → [] 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.
What is Regex?
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 []. 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:
the.{3}
Matches the word “the” followed by any three characters
[0-9]{4}\s
Matches any 4 number characters in a row followed by a space
For educational purposes, we are going to refer to Regex as a small little robot. A small little robot that takes commands (in the form of regexExpressions) and then looks for those patterns in text.
How to use Regex in Your Docs
One could, and some have, 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. Need to Know Patterns (Aka - Regex Expressions)
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
Regex with Addresses
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.
Address Line Only
What pattern(s) did this solution use? Select any that apply.
This solution used pattern 1. State
What pattern(s) did this solution use? Select any that apply.
This solution used pattern 1, 3, and 5. 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.
Regex with Larger Text
The 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.
Valid Email Address
What pattern(s) did this solution use? Select any that apply.
This solution used pattern 1, 6 and 7. 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! Price Service
What pattern(s) did this solution use? Select any that apply.
This solution used pattern 8. 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.