substring

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

Example Formulas

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: ●●●●●○○○○○ */

Example Database

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.

View and Duplicate Database

substring

“Progress” Property Formula

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.

Other formula components used in this example: