How can I append additional text to strings pulled via formula from other cells?

I am using a large function to pull together a list of names based on whether someone has provided an update. The formula that I am am using is basically…

=IF(Input from Cell X = "", [Contents from Cell X1], "") + ", " + IF(Input from Cell Y = "", [Contents from Cell Y1], "") + repeat.

This works but it gives me lists with a bunch of empty commas (, , , Contents from Cell X1, , , Contents from Cell Y1). I want to bring the comma and space inside the IF function so that I only get the comma and the space if I am adding text and otherwise there is just nothing.

Basically something like

=IF(Input from Cell X = "", [Contents from Cell X1] ", ", "")

This doesn't work though. I guess another way to ask this question is how to I append text to cell references inside formulas?

THANK YOU!

Find more posts tagged with

Formulas

Accepted answers

=Chris Palmer

You can explore across the row with something like this..

=IF([Input X]@row <> "", [Name X]@row + ", ", "") + IF([Input Y]@row <> "", [Name Y]@row + ", ", "") + IF([Input Z]@row <> "", [Name Z]@row + ", ", "")

It may result with a single trailing comma at the end, so to remedy that you could use LEN and LEFT

=IF( LEN( IF([Input X]@row <> "", [Name X]@row + ", ", "") + IF([Input Y]@row <> "", [Name Y]@row + ", ", "") + IF([Input Z]@row <> "", [Name Z]@row + ", ", "") ) > 2, LEFT( IF([Input X]@row <> "", [Name X]@row + ", ", "") + IF([Input Y]@row <> "", [Name Y]@row + ", ", "") + IF([Input Z]@row <> "", [Name Z]@row + ", ", ""), LEN( IF([Input X]@row <> "", [Name X]@row + ", ", "") + IF([Input Y]@row <> "", [Name Y]@row + ", ", "") + IF([Input Z]@row <> "", [Name Z]@row + ", ", "") ) - 2 ), "" )

All comments

=Chris Palmer

Hello,

Something like this perhaps?

=JOIN(COLLECT([Name Column]:[Name Column], [Input Column]:[Input Column], <> ""), ", ")

Phil Yorkville

https://community.smartsheet.com/discussion/comment/483325#Comment_483325

This would work, unfortunately, my data is not in a column but spread out across multiple cells in a row. I cannot change this as I am supporting another Team's data set. I need to be add to pick and choose specific cells in a row to join together.

I am still curious about my original question, there has to be a way to add text to a cell referenced within a formula.

Thank you for your reply.

=Chris Palmer

Phil Yorkville

https://community.smartsheet.com/discussion/comment/483334#Comment_483334

Thanks, Chris! I did find a solution to my original problem which was just to add a + between my cell reference and additional text.

=Chris Palmer

Awesome! Happy to help :-)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.