Skip to main content

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

 

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.

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.

Reply


Terms & Conditions