String with "and"

Is it possible to write a formula so I can get a list of items from a column and have the word “and” appear before the last item regardless if there are 2 or 20 items in that column? For example, I want it to say “black, white, and red.”

Is there a better way? Probably! But its late and this is the first thing that came to mind!

Essentially, what im doing is using Splice() to insert a value before the last value. The specific value Im inserting to your list is the word “and”

After that, I join the entire new list (with the “and” addition) with a comma and a space.

This joins every item with a comma and space including the word “and” which is not what you want. So lastly, I simply replace and, with and and BOOM. you are done

1 Like

hi @Joanna_Lamb

My contribution is a bit different from the @Scott_Collier-Weir suggestion. I’d like to see the comma removed a the one but last list item.

Actually we have two lists we glue together using ‘and’

  1. the part containing all items but not the last
  2. the last item
Format("{1} and {2}",

you may dislike the @ reference. To get rid of that you turn all items into text.

Format("{1} and {2}",

Enjoy, Cheers, Christiaan


This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.