Exclude from concatenated text if text equals "xxx"

Caroline Elliott

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!

jmyzk_cloudsmart_jp

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

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

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

jmyzk_cloudsmart_jp

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

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

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

Caroline Elliott

That is brilliant! Thank you so much, it was driving me crazy.

jmyzk_cloudsmart_jp

Happy to help!😁