Join columns, not side by side, and skip columns with no info

Mona Singh

I am looking to join multiple columns using a "-" as deliminator and I dont have the columns side by side to select range and use Join function.

I also want it to skip columns that are blank.

Also - I notice in Smartsheet i have to use date format 10/15/2020 is there a way to change it to 10-15-20 (I use "-" as separator and want to keep consistency in date)

Help!

Andrée Starå

Hi Mona,

Join

Try something like this.

=JOIN(COLLECT(ColumnA@row:ColumnG@row; ColumnA@row:ColumnG@row; NOT(ISBLANK(@cell))); " - ")

The same version but with the below changes for convenience.

=JOIN(COLLECT(ColumnA@row:ColumnG@row, ColumnA@row:ColumnG@row, NOT(ISBLANK(@cell))), " - ")

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

https://help.smartsheet.com/function/join

https://help.smartsheet.com/function/collect

Date

Have you checked the available options in the Date toolbar?

https://help.smartsheet.com/articles/2479481-apply-standardized-date-formats-in-your-sheet

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Mona Singh

Thanks Andre - I totally missed another major point to my question.

I have 2 categories to get the right column.

IF (State selected) and (Segment Selected) Then return to me only the segments that match for that state. This could mean that it's Pennsylvania for Enterprise AND Commerical. (more than one segment can be selected in the segment column) and then i want to connect those cells into one, using delimiter.

Logic would be

IF event is in Alabama, and Enterprise and MB are selected, only return those columns, otherwise leave blank. And my second coding would be in "targeted Region, to concatenate the columns with information to get to our Targeted Region.

Mona Singh

I dont know if we can match 3 criterias, but would love to do that too...

Ill keep poking around online too.

Andrée Starå

@Mona Singh

Excuse my late response.

How did it go?

Did you get it working?

Mona Singh

I talked to someone and I'd have to make a bunch of If, ISBLANK statements and it was going to be a very long code to write. For now, i did not have time to go write that code and have a work around. When my world slows down, I hope to go back and see if i can write it.

Andrée Starå

@Mona Singh

Ok. Let me know if I can help!