Combining multiple cells when one is blank

I am using this formula to combine cells in a single row to create a unique name/identifer. This formula works fine.

=Suite@row + "_" + Offering@row + "_" + Module@row + "_" + [Additional Asset Name Detail (as needed)]@row + "_" + [Asset Type]@row + "" + "_" + FY@row

Producing this result.

MA_Test 2 offering_Test 2 Module_Additional Name_FG_FY22

The issue come in because the [Additional Asset Name Detail (as needed)] data is OPTIONAL. When nothing is entered in this cell then the formula produces the double underline between MODULE and FG, as seen here.

MA_Test 2 offering_Test 2 Module__FG_FY22

How can is use ISBLANK or some other function to remove this extra underline when the optional field is blank?

Find more posts tagged with

Accepted answers

Andrée Starå

Hi @Jeana

To add to Bassam's excellent advice/answer.

Another method would be to use a combination of JOIN COLLECT.

Try something like this.

=JOIN(COLLECT(Suite@row:FY@row, Suite@row:FY@row, <>""), "_")

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

All comments

Bassam Khalil

Hi @Jeana

Hope you are fine, Try to use the following formula:

=IFERROR(IF(ISBLANK([Additional Asset Name Detail (as needed)]@row), Suite@row + "_" +
Offering@row + "_" + Module@row + "_" + [Asset Type]@row + "" + "_" + FY@row, Suite@row +
"_" + Offering@row + "_" + Module@row + "_" + [Additional Asset Name Detail (as needed)]@row +
"_" + [Asset Type]@row + "" + "_" + FY@row), "")

the following screenshot shows the result:

Jeana

Thanks!!!

Jeana

So if it's possible for ANY of the other cells to be blank would it look like this with the other condition added or do I need an OR in there somewhere?

=IFERROR(IF(ISBLANK([Additional Asset Name Detail (as needed)]@row), Suite@row + "_" + Offering@row + "_" + Module@row + "_" + [Asset Type]@row + "" + "_" + FY@row, Suite@row + "_" + Offering@row + "_" + Module@row + "_" + [Additional Asset Name Detail (as needed)]@row + "_" + [Asset Type]@row + "" + "_" + FY@row), "", (IFERROR(IF(ISBLANK([Suite]@row), [Additional Asset Name Detail (as needed)]@row + "_" + Offering@row + "_" + Module@row + "_" + [Asset Type]@row + "" + "_" + FY@row, Suite@row + "_" + Offering@row + "_" + Module@row + "_" + [Additional Asset Name Detail (as needed)]@row + "_" + [Asset Type]@row + "" + "_" + FY@row), ""))

Andrée Starå

Hi @Jeana

To add to Bassam's excellent advice/answer.

Another method would be to use a combination of JOIN COLLECT.

Try something like this.

=JOIN(COLLECT(Suite@row:FY@row, Suite@row:FY@row, <>""), "_")

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Jeana

SWEET!!! That's simpler in this case.

Thanks Andrée!

Andrée Starå

@Jeana

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Bassam Khalil

@Jeana

You are welcome and I will be happy to help you any time, please help the Community by accepting the answer.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.