Ignoring Blank Rows in a List
Hello,
Trying to figure out how to display results in a cell that will ignore blank rows. I have a cell that lists results in the following manner:
Site 1
Site 2
Site 4
Site 5
Site 7
Is there a way to list the results without the blank rows between site 2 and 4 and between site 5 and 7?
cell formula: =[Helper Site 1 ID]@row + " " + [Helper Site 1 Name]@row + CHAR(10) + [Helper Site 2 ID]@row + " " + [Helper Site 2 Name]@row + CHAR(10) + [Helper Site 3 ID]@row + " " + [Helper Site 3 Name]@row + CHAR(10) + [Helper Site 4 ID]@row + " " + [Helper Site 4 Name]@row + CHAR(10) + [Helper Site 5 ID]@row + " " + [Helper Site 5 Name]@row + CHAR(10) + [Helper Site 6 ID]@row + " " + [Helper Site 6 Name]@row + CHAR(10) + [Helper Site 7 ID]@row + " " + [Helper Site 7 Name]@row + CHAR(10) + [Helper Site 8 ID]@row + " " + [Helper Site 8 Name]@row + CHAR(10) + [Helper Site 9 ID]@row + " " + [Helper Site 9 Name]@row + CHAR(10) + [Helper Site 10 ID]@row + " " + [Helper Site 10 Name]@row + CHAR(10)
Answers
-
Hi @MichaelO1 , The way I would do this would be to use SUBSTITUTE() like this:
= SUBSTITUTE(<<Your long expression>>, CHAR(10)+" "+CHAR(10), CHAR(10))
Basically substitute anywhere you have two CHAR(10)s in a row (accounting for the space) with a single CHAR(10).
Since you appear to be toward the beginning of your design, I would also consider whether JOIN() could be helpful... it wouldn't help in the way you have things set up now, but I might rearrange things a bit to make it work. That would be easier for someone to troubleshoot down the road.
Hope this helps. Be well!
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
-
Hi @MichaelO1
I hope you're well and safe!
You could use a JOIN COLLECT combination.
Here's an example.
=JOIN(COLLECT([1st Dept]@row:[Last Dept]@row, [1st Dept]@row:[Last Dept]@row, @cell <> ""))
Would that work/help?
I hope that helps!
Be safe, and have a fantastic week!
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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
With using the JOIN(COLLECT function I receive the following result:
=JOIN(COLLECT([Helper Site 1 ID]@row:[Helper Site 10 ID]@row, [Helper Site 1 ID]@row:[Helper Site 10 ID]@row, @cell <> ""))
How would I format the result to show:
111 site 1
222 Site 2
444 Site 4
555 Site 5
777 Site 7
888 Site 8
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!