Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 22, 2023 01:12 PM
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:
Jun 22, 2023 01:49 PM
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.
Jun 24, 2023 04:38 AM
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.