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
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’
- the part containing all items but not the last
- the last item
Format("{1} and {2}",
Lamb.Slice(1,Lamb.Count()-1).List(),
Lamb.Last())
you may dislike the @ reference. To get rid of that you turn all items into text.
Format("{1} and {2}",
Lamb.Slice(1,Lamb.Count()-1).List(),
Lamb.Last()).ForEach(CurrentValue.ToText())
Enjoy, Cheers, Christiaan
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.