Ignoring Blank Rows in a List

MichaelO1 · 03/26/24

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)

Scott Orsey · 03/26/24

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!

Andrée Starå · 03/26/24

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!