Exclude from concatenated text if text equals "xxx"

Caroline Elliott
Caroline Elliott ✭✭✭✭
in Formulas and Functions

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.

image.png

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.

image.png

Any ideas would be greatly appreciated!

Tags:
· Share on FacebookShare on Twitter

Best Answer

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

    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, ".

    https://app.smartsheet.com/b/publish?EQBCT=2b0f71c668034c2fb582b8468ace6e9d
    image.png

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

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


    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!