Skip to main content

Combine list of people's names and add "and" if if it is between last two (or only two) people

  • June 22, 2023
  • 2 replies
  • 46 views
D
Forum|alt.badge.img+5

I'm building a formula to roll up a list of people's names only if they are filled. I would like the formula to result in:

[Person 1], [person 2], [person 3], and [person 4]

Sometimes there is no person 2, 3, or 4 listed. Right now i have the formula listing all of the people and skipping where it is blank. I'd like it to add an "and" between the last two people listed. This is what I have so far:

 

REGEX_REPLACE(
CONCATENATE(
IF({Staff Name 1}, {Staff Name 1} & ", "),
IF({Staff Name 2}, {Staff Name 2} & ", "),
IF({Staff Name 3}, {Staff Name 3} & ", "),
IF(ARRAYUNIQUE({Advisor Name}, {Advisor Name} & ", "))
),
", $",
""
)

 

2 replies

S
Forum|alt.badge.img+18

There's not really a great way to do this with formulas. You would have to add "and" to every person and then have as many regex_replace or substitute statements as there are people minus one to get rid of the unnecessary "and"s.

Alexey_Gusev
Forum|alt.badge.img+25

Hi,
You can count number of commas in your result and substitute last comma with 'and'. Default SUBSTITUTE used with 3 parameters (Text, change_from, change_to)  and replace all occurences. You can add fourth parameter - which occurence to change. 
To count number of commas, use LEN(Text)-LEN(SUBSTITUTE(Text, ',' ,  '' )
I would recommend to use text editor (at least Notepad) to prepare something like
SUBSTITUTE(Text, ',' , ' and ' , LEN(Text)-LEN(SUBSTITUTE(Text, ',' ,  '' ) )
and then replace Text by your formula.
Result formula will be quite large and complex, but it's OK, I have some formulas listed on  several notepad pages..

Such task might be done in more 'clever' way depending on possible data layout, for example whether if Staff Name 3 filled , that means 1 and 2 are present. But that's a long time to clarif all details.

Terms & ConditionsAccessibility statement