replaceAll() function searches a string for a pattern (which can be a regular expression), and replaces ALL matches it finds with another string.
replaceAll(string, string [regex supported], string [regex supported])
Since they can search for pattern matches using regular expressions,
replaceAll() and its counterpart, replace, are two of the most versatile and powerful functions you can use in your Notion formulas.
Good to know:
replaceAll(), replace, and test are able to automatically convert numbers and Booleans (but not dates) to strings. Manual type conversion is not needed.
replaceAll("Dogs Dogs Dogs","Dogs","Cats") // Output: Cats Cats Cats // Matches are case-sensitive replaceAll("Dogs dogs Dogs","Dogs","Cats") // Output: Cats dogs Cats // You can use brackets  to create a set of characters, // any of which will be matched replaceAll("Dogs dogs Dogs", "[Dd]ogs", "Cats") // Output: Cats Cats Cats // You can also create a group with () and then use the | (OR) operator replaceAll("Dogs dogs Dogs", "(D|d)ogs", "Cats") // Cats Cats Cats // Accepts regex metacharacters, such as "\\b" which denotes "word boundary". // Without \\b, this would output "Thwas was Sparta" replaceAll("This is Sparta","\\bis\\b","was") // Output: This was Sparta // replaceAll() is a great way to count elements in a string. // Do this by using a regular expression to remove all characters // except the commas that separate the elements (see the example // database below for an in-depth look at this) replaceAll("Dog, Cat, Monkey, Bat, Gorilla","[^,]","") // Output: ,,,, // Apply length() + 1 to get the count!
Check out the regular expressions page to see a lot more that you can accomplish using them!
This example database uses a multi-select property to track which superpowers each hero has. The Count formula uses
replaceAll() to output a superpower count for each hero.
View and Duplicate Database
“Count” Property Formula
// Compressed prop("Name") + " has " + format(length(replaceAll(prop("Powers"), "[^,]", "")) + 1) + ((length(replaceAll(prop("Powers"), "[^,]", "")) < 1) ? " power." : " powers.") // Expanded prop("Name") + " has " + format( length( replaceAll( prop("Powers"), "[^,]", "" ) ) + 1 ) + ( ( length( replaceAll( prop("Powers"), "[^,]", "" ) ) < 1 ) ? " power." : " powers." )
When you call a multi-select property from within a formula, you end up with a single string where each select value is separated by a comma – e.g. “Super Strength, Ice Breath, Flight”.
As long as your multi-select options don’t contain commas themselves, we can take advantage of this fact. Since each value is separated by a comma, all we need to do is count the commas and then add one.
“Super Strength, Ice Breath, Flight” has two commas. Adding one to that number gets us the total number of select options: three.
But how can we count the commas? That’s where
replaceAll() comes in.
In our formula, we use
replaceAll(prop("Powers"), "[^,]", "") to replace ALL instances of any character that isn’t a comma with
"" – an empty value. In effect, this removes all characters except the commas.
We target these characters with the regular expression
- The brackets
define a set of characters where any of them will be part of the match. E.g. a regular expression like
[Dd]ogwould match both “Dog” and “dog”.
- Since we’re using
replaceAll()instead of replace, using the brackets ensures that every character in our search string that matches a character in the brackets will be replaced.
- Since we’re using
- The caret
^used within the brackets
is a flag that says, “match a character that is not defined in the brackets”. – e.g.
[^d]would match any character that isn’t “d”.
- In the brackets, we define the comma
,as the character after the caret.
[^,] says, “match any character that isn’t a comma”.
This results in a string of commas – e.g. “Super Strength, Ice Breath, Flight” becomes “,,”.
From there, we can use the length function to get the length of our resulting string.
Adding 1, we get the actual number of select tags in the row – in the case,
Finally, we use format and do some string concatenation with the add (
+) operator in order to create a sentence like, “Superman has 3 powers”.
The formula ends with another usage of this
length() chain within an if statement (using the conditional operators
:) in order to determine if the sentence should end with “power” or “powers”.