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

[Dara_Molotsky]

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} & ", "))

),

", $",

""

)

[Stephen_Orr1]

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]

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.