The replace() function searches a string for a pattern (which can be a regular expression), and replaces the first match it finds with another string.
replace(string, string [regex supported], string [regex supported]) string.replace(string [regex supported], string [regex supported])
Since they can search for pattern matches using regular expressions, replace(),its counterpart, replaceAll, and the related test function are three of the most versatile and powerful functions you can use in your Notion formulas.
Good to know: replace(), replaceAll, and test are able to automatically convert numbers and Booleans (but not dates) to strings. Manual type conversion is not needed.
Example Formulas
replace("Pogo","Po","Dog") /* Output: Doggo */
/* Matches the first occurrence, unless otherwise specified */
replace("Dogs Dogs Dogs","Dogs","Cats") /* Output: Cats Dogs Dogs */
/* $ tells the regex engine "start from end of line and work backwards" */
replace("Dogs Dogs Dogs","Dogs$","Cats") /* Output: Dogs Dogs Cats */
/* Matches are case-sensitive */
replace("thomas","t","T") /* Output: Thomas */
/* You can use brackets [] to create a set of characters, any of which will be matched */
replaceAll("thomas", "[Tt]homas", "Megatron") /* Output: Megatron */
/* You can also create a group with () and then use the | (OR) operator */
replaceAll("thomas", "(T|t)homas", "Megatron") /* Megatron */
/* Accepts regex metacharacters, such as "\b" which denotes "word boundary". Without \b, this would output "Thwas is Sparta" */
replace("This is Sparta","\bis\b","was") /* Output: This was Sparta */
Check out the regular expressions page to see a lot more that you can accomplish using them!
Example Database: File Extensions
The example database below contains several media attachments. The File Extension property uses replace() and a regular expression to replace each file’s full URL with its file extension.
View and Duplicate Database
“File Extension” Property Formula
replace(prop("File"), ".*\.(\w+)$", "$1")
To extract the file extension from the media attachment’s full URL, we use a regular expression that essentially translates to:
Start from the end of the string and capture all characters until the first period
.– then replace the entire URL with those captured characters.
Let’s break down exactly how our regular expression – .*\.(\w+)$ – does this:
.is a wildcard, meaning it’ll match any character.*is a quantifier. It tells the regex engine to match zero or more of the preceding character. So.*means, “match any number of any character.\.translates to an actual period.character. It must be “escaped” with\because.is a special wildcard character in regular expressions (as mentioned above).(\w+)is our capture group. We’re “capturing” any match defined within the parentheses(), allowing us to reference the capture later (which we do with the$1).\wis another special character that translates to “any word character”, and+is a quantifier that translates to one or more.$is an anchor that tells the regex engine that the match must happen at the end of the input string. This essentially makes sure the match happens at the end of the URL.
Basically, we’re telling the regular expression to match the entire URL, but we’re putting only the file extension (everything after the final period . character) into a capture group.
Then, we’re replacing that entire matched URL with the contents of the capture group by referencing it with $1.
Good to know: If you want to learn more about using regular expressions in Notion, check out my complete regular expression reference.
Most truly worthwhile use cases for replace() in Notion will use special characters like the ones shown above, so it’s worth getting familiar with them!
Example Database: Temperature Conversion
The example database below shows how you can use replace() in conjunction with other functions to convert a temperature value from Fahrenheit to Celsius.
At first, the temperature is a string value that cannot be manipulated by arithmetic functions. replace() is used to extract the number from the full string.
View and Duplicate Database
“Celsius Conversion” Property Formula
replace(
replace(
prop("Name"),
"\d+",
format(
round(
(
toNumber(
replace(
prop("Name"),
"\D*(\d+)\D",
"$1"
)
) - 32
) * 5 / 9
)
)
),
"°F",
"°C"
)
This formula is meant to demonstrate how you can use replace() in conjunction with other functions in order to do things that are otherwise impossible.
Here, the challenge is to convert the weather prediction from Fahrenheit to Celsius. However, the prediction starts out as a string value – i.e. “Today’s high will be 78 °F”.
Since it’s a string, we can’t currently use arithmetic functions on it. We also can’t currently use toNumber to convert it to a number, since it contains non-numeric characters.
The slice function isn’t an option, either. The numeric temperature shows up at different positions based on the text that comes before it.
Fortunately, the replace() function allows us to solve this problem using a few simple regular expressions.
Here’s a quick breakdown of this formula’s process:
- Extract the temperature number using
\D*(\d+).\D, which captures any numeric digits and replaces the entire string with just those digits. See the first example on this page to understand how that works. (\Dmeans “any non-digit character”, and\dmean “any digit character”.) - Convert the digits from a string to a number using toNumber.
- Apply the Fahrenheit-to-Celsius conversion formula:
(Fahrenheit temp - 32) * 5 / 9. - Round to the nearest integer using round.
- Convert the result to a string using format.
- Use
replace()a second time to replace the original weather predictions Fahrenheit number with out new Celsius number. - Use
replace()a final time to replace°Fwith°C.
Other formula components used in this example:
