Gallery
Kuovonne's Guide to Airtable
Share
Explore
Gallery
Kuovonne's Guide to Airtable
Specific Solutions

Ending a list of rollup items with ", and"

⁠
⁠
Tip Jar
⁠
Thank Kuovonne for creating this content!
⁠
⁠
⁠
⁠
⁠

Inspiration

When rolling up items in linked records, it is very common to combine linked items into a comma separated list. However, if you are including the list in a larger paragraph, you may want to include the word and before the last item in the list.
If there is only one item in the list, there should be no comma and no and.
If there are two items in the list, there should be no commas, and the two items should be joined with the word and.
If there are three or more items in the list, there should be a comma after every item, except between the last two items, where there should be the word and.
⁠
image.png
⁠
⁠
If you are a fan of the Oxford comma, you may want a comma before the and, but that formula is more complex and not covered here.

Building a comma separated list

Start by creating a comma separated list of linked values using a rollup field with a simple formula function:
ARRAYJOIN(values, ", ")
ok
Make sure you are using a rollup field, not a lookup field. The commas you see in a lookup field do not really exist.

Failings of traditional text functions

The next step is to remove the last comma and replace it with the word and. Trying to do this with regular text functions like FIND(), SEARCH(), REPLACE(), or SUBSTITUTE() is difficult because ...
you need to look from the end of the text string, not the start
you need to leave any commas before the last one in place
there could be any number of commas

Using RegEx (regular expression)

Regular expressions are very good at the type of search and replace we need.

Identifying the last item in a new formula field

Let’s start by creating a new formula field using REGEX_MATCH() to find the last item.
Each item in the list is composed of one or more characters that are not commas. Use the ^ control character in front of a comma , to indicate any character that is not a comma. Place this combination inside square brackets []. Then use the + control character to indicates one or more matches.
REGEX_MATCH(
{Comma Separated Rollup Field},
"[^,]+"
)
As is, this regular expression will give us the first item in the list. In order to get the last item in the list, use the $ control character to indicate that our item must be followed by the end of the line.
REGEX_MATCH(
{Comma Separated Rollup Field},
"[^,]+$"
)
Include the preceding comma and space in the pattern.
REGEX_MATCH(
{Comma Separated Rollup Field},
", [^,]+$"
)

Performing the replacement

Because we want to replace text, not just find it, swap out REGEX_MATCH() with REGEX_REPLACE(). Start with a placeholder empty text string for the replacement value.
REGEX_REPLACE(
{Comma Separated Rollup Field},
", [^,]+$",
""
)
Because we want to replace only the comma and space, and not the last item, use grouping parenthesis () to indicate these two different groups of characters.
REGEX_REPLACE(
{Comma Separated Rollup Field},
"(, )([^,]+)$",
""
)
Now we move to the replacement text string. Let’s start by using $2 to keep the the second group in the matched text, which is the last item in the list. We can ignore the first capture group $1 because we don’t want that comma.
REGEX_REPLACE(
{Comma Separated Rollup Field},
"(, )([^,]+)$",
"$2"
)
We also want to include the word and with spaces around it before the last item in the list.
REGEX_REPLACE(
{Comma Separated Rollup Field},
"(, )([^,]+)$",
" and $2"
)

Combining the helper formula with the initial rollup formula

REGEX_REPLACE(
ARRAYJOIN(values, ", "),
"(, )([^,]+)$",
" and $2"
)
⁠
⁠
⁠
Thank Kuovonne for creating this content!
⁠
Give Kuovonne a sandwich
⁠
Signup for Coda w/ referral link
⁠
⁠
⁠
Buy from my Gumroad Store
⁠
⁠
⁠
⁠
⁠
⁠
⁠

Gallery
Share
 
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.