I’m working on an amateur project that involves outputting nice-looking text. I managed to cobble together something that serves my specific needs and thought I’d share it here in case it helped anyone else (and also so I can save the link for reference later).
Many thanks go to @W_Vann_Hall for the sample base demonstrating how to extract the last name from a field, which was a crucial piece in my “finding the last comma” puzzle. Is there a PROPER name formula in Airtable? - #3 by W_Vann_Hall
Goal: Given a list of items separated by commas, add an Oxford comma where appropriate
My data has a rollup field which returns items separated by a comma and a space. Example:
Moonbreaker Mountain
Dreamwater Depths, Meltwater Loch
Meltwater Loch, Dreamwater Depths, Blastfire Bog
In lists containing one item, there’s nothing to change.
In lists containing two items, I want the comma replaced with " and".
In lists containing three or more items, I want the FINAL comma replaced with “, and” (preserving the comma itself).
eg:
Moonbreaker Mountain
Dreamwater Depths and Meltwater Loch
Meltwater Loch, Dreamwater Depths, and Blastfire Bog
I accomplished this with the following formula, where {Locale - All} is the list of items.
IF(
FIND(
",",
SUBSTITUTE(
{Locale - All},
",",
" and",
LEN({Locale - All}) -
LEN(
SUBSTITUTE(
{Locale - All},
","
,""
)
)
)
) >0,
SUBSTITUTE(
SUBSTITUTE(
{Locale - All},
",",
" and",
LEN({Locale - All}) -
LEN(
SUBSTITUTE(
{Locale - All},
",",
""
)
)
),
" and",
", and"
),
SUBSTITUTE(
{Locale - All},
",",
" and",
LEN({Locale - All}) -
LEN(
SUBSTITUTE(
{Locale - All},
",",
""
)
)
)
)
This formula has a few key parts:
1) Index of the final comma
As suggested by @W_Vann_Hall, we use two LEN() statements to find the index of the final comma. This gets referenced a lot.
LEN({Locale - All}) - LEN(SUBSTITUTE({Locale - All},",","")
The second LEN() is the input string with all the commas deleted (substituted with nothing).
Subtracting that from the length of the total string is equivalent to the number of commas removed, aka the number of the final comma.
2) An un-Oxford list
First we use this index to substitute the final comma with “and”. We could stop here if we didn’t care about using the Oxford comma. But we won’t because we’re perfectionists.
SUBSTITUTE(
{Locale - All},
",",
" and",
LEN({Locale - All}) -
LEN(
SUBSTITUTE(
{Locale - All},
","
,""
)
)
)
3) Restore the final comma in a longer list
We wrap that substitution in a FIND() statement to count the number of commas left. If there are none, we leave it be. This is the case when the list contains 2 or fewer items.
But if there is more than one comma still there, it means that the list has three or more items. We want the final item to be separated by a comma too, so we need to add the one back in that we just replaced with " and".
SUBSTITUTE(
SUBSTITUTE(
{Locale - All},
",",
" and",
LEN({Locale - All}) -
LEN(
SUBSTITUTE(
{Locale - All},
",",
""
)
)
),
" and",
", and"
)
This method works for my data because I know that a) the only commas in the text of my rollup field are the ones separating list items, and b) none of the incoming text includes the substring “and”. I’d have to add a few more modifications to escape those cases if I needed to deal with them. I should also note that in applying this formula to lookup fields, I had to wrap the field name in ARRAYJOIN() to convert it to a string as instructed by this help topic: Array functions – Airtable Support
I hope anyone reading this finds it a useful stepping stone in their own project. (Unless I’ve reinvented the wheel and there’s a much simpler way to do all of this, in which case I’d love to hear it. :grinning: )
