Exclude from concatenated text if text equals "xxx"

Options

I am trying to concatenate text from 10 cells that are not next to each other into 1 while excluding text that is equal to "Not Required".

This is the formula I am using to concatenate the text. I have also tried the Join function without success.

This is the result, which is correct, but as you can see it excluding "Not Required" would be a lot more presentable in reports, dashboards and other sheets this is linked to.

Any ideas would be greatly appreciated!

Tags:

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/21/24 Answer ✓
    Options

    Hi @Caroline Elliott

    I would use the JOIN, DISTINCT, and SUBSTITUTE combination.

    =SUBSTITUTE(JOIN(DISTINCT([Reviewer 1]@row:[Reviewer 10]@row), ", ") + ", ", "Not Required" + ", ", "")

    DISTINCT([Reviewer 1]@row:[Reviewer 10]@row)

    gives unique Reviewers, including "Not Required"

    JOIN(DISTINCT([Reviewer 1]@row:[Reviewer 10]@row), ", " )

    concatenates Reviewers with ", " delimiter.

    SUBSTITUTE(JOIN(DISTINCT([Reviewer 1]@row:[Reviewer 10]@row), ", ") + ", ", "Not Required" + ", ", "")

    add ", " at the end and remove "Not Required"+ ", " or "Not Required, ".

    If you want to remove the last ", ", use this formula.

    =LEFT([Concept Reviewers]@row, LEN([Concept Reviewers]@row) - 1)


Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!