replaceAll

The 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])

string.replaceAll(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.

Example Formulas

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 */

Check out the regular expressions page to see a lot more that you can accomplish using them!

Example Database

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

Notion – The all-in-one workspace for your notes, tasks, wikis, and databases.

A new tool that blends your everyday work apps into one. It’s the all-in-one workspace for you and your team

thomasfrank.notion.site

“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.

For example, “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 [^,]:

1. The brackets [] define a set of characters where any of them will be part of the match. E.g. a regular expression like [Dd]og would 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.
2. 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”.
3. In the brackets, we define the comma , as the character after the caret.

In effect, [^,] 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. length(",,") = 2.

Adding 1, we get the actual number of select tags in the row – in the case, 3.

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 replaceAll() → length() chain within an if statement (using the conditional operators ? and :) in order to determine if the sentence should end with “power” or “powers”.

Other formula components used in this example:

add – Thomas Frank

thomasjfrank.com

format – Thomas Frank

thomasjfrank.com

length – Thomas Frank

thomasjfrank.com

if – Thomas Frank

thomasjfrank.com

smaller – Thomas Frank

thomasjfrank.com