New in Formulas 2.0

The substring() function allows you to “slice” up a string and output a smaller piece of it.

substring(string, number, number [optional]) string.substring(number, number [optional])

It accepts three arguments:

  1. The input string
  2. A starting index, which is included in the output string (the start index of the input string is 0).
  3. An ending index, which is optional and excluded from the output string.

Good to know: substring() works best when you know the indexes you want to use, or when they need to be mathematically derived (e.g. based on a percentage, such as “% of tasks completed”).
If you need to define your index by matching a pattern (e.g. “Get the middle name” from a Full Name field), you should use replace or replaceAll instead (or combine them with slice).

substring("Dangerfield", 0, 6) /* Output: Danger */ "Monkey D. Luffy".substring(0, 6) /* Output: Monkey */ substring("Monkey D. Luffy", 10) /* Ouput: Luffy */ substring("●●●●●●●●●●", 0, 6) + substring("○○○○○○○○○○", 0, 6) /* Output: ●●●●●○○○○○ */

This example database uses the substring() function to create a simple progress bar. The output of the progress bar is determined by the Percent number property.


substring( "●●●●●●●●●●", 0, prop("Percent") * 10 ) + substring( "○○○○○○○○○○", 0, (1 - prop("Percent")) * 10 ) + " " + (prop("Percent") * 100) + "%"

This formula creates a progress bar by using two instances of the slice() function, along with the unicode characters  and  (see this Unicode character reference for more options).

  1. The first substring() starts with ●●●●●●●●●● as the input string. There are 10 symbols. We start at index 0, and then define the ending index using the Percent property.
    • Note that we have to multiply prop("Percent") by 10, since a percent value like 40% actually equates to 0.4. Since there are 10  characters in our input string, we need an ending index between 0 and 10.
  2. The second substring() does nearly the same thing with the string: ○○○○○○○○○○, which represents the “empty” part of the progress bar.
    • To find the ending index for this string, we use (1 - prop("Percent")) * 10 in order to get the remaining percentage. E.G. if Percent is set to 40% (which would create an end index of 4), we want to set this ending index to 6.
  3. Finally, the two strings are concatenated. We also show the actual percentage in the string for good measure.
About the Author

My name is Thomas Frank, and I'm a Notion-certified writer, YouTuber, and template creator. I've been using Notion since 2018 to organize my personal life and to run my business and YouTube channel. In addition to this formula reference, I've created a free Notion course for beginners and several productivity-focused Notion templates. If you'd like to connect, follow me on Twitter.

🤔 Have an UB Question?

Fill out the form below and I’ll answer as soon as I can! ~Thomas

🤔 Have a Question?

Fill out the form below and I’ll answer as soon as I can! ~Thomas