# Ignoring Blank Rows in a List

Options
✭✭✭

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)

• ✭✭✭✭✭
edited 03/26/24
Options

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!

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!