Rich text in formulas, lookups and rollups

Formulas, lookups, and rollups of long text fields with rich text enabled will use the field’s plain text plus the characters automatically added for lists. We do not currently support rich text formatting in formula, lookup, and rollup field.

In this example, the notes for an issue are in rich text.

When those notes are used in a rollup field, the plain text representation is shown.

Formulas

Counting the number of checked items in rich text

Because checkboxes are converted to “[ ]” for unchecked and “[x]” for checked, a formula can be used to count the number of times these characters occur.

Unchecked items: (LEN({Content})-LEN(SUBSTITUTE({Content},"[ ]","")))/3
Checked items: (LEN({Content})-LEN(SUBSTITUTE({Content},"[x]","")))/3

Using the above formula fields, the percent of checkboxes checked can be calculated with the following formula:

IF({Unchecked items}+{Checked items}=0,BLANK(),IF({Checked items}>0,{Checked items}/({Unchecked items}+{Checked items}),0))

• If no checkboxes in the rich text, returns blank.
• If no checked checkboxes in rich text but there are checkboxes, returns 0.

Line breaks and list styles are included in formulas that count characters

Similar to long text, line breaks are included for LEN(), RIGHT(), and LEFT() function. The plain text for lists also add to the number of characters counted.