Reformat as a Bulleted List

[Jason_F]

Hey Community:

I built a pretty extensive base that tracks all the places a band has been. I want to be able to have a formula that will take this field’s output and make is a list. I will never know where the commas may fall for the names of the places.

A sample result in my field is the following.

, , , , , , , , , , “12-11-20 - New York, NY
Rockefeller Center”, , , “10-27-18 - Bridgeport, CT
BRYAC Restaurant & Raw Bar”, ,

[Justin_Barrett]

What type of field is creating that output? It would be best to clean up the output first before piping it into a formula to turn it into a bullet-point list.

[Jason_F]

It’s a formula field that is looking across all my fields in that row and combining them into one.

I’d love to clean it up so it looks at the cell and if it’s blank it skips it and if it has a value it adds to the list.

[Jason_F]

And do this over the course of about 28 columns.

[Justin_Barrett · Accepted solution]

Jason_F:

I’d love to clean it up so it looks at the cell and if it’s blank it skips it and if it has a value it adds to the list.

Jason_F:

And do this over the course of about 28 columns.

That’s definitely doable. The formula structure would look like this:

TRIM(
IF({Field 1}, "• " & {Field 1}) &
IF({Field 2}, "\n• " & {Field 2}) &
IF({Field 3}, "\n• " & {Field 3}) &
...
)

This variation would also get rid of the quotes around each entry:

SUBSTITUTE(TRIM(
IF({Field 1}, "• " & {Field 1}) &
IF({Field 2}, "\n• " & {Field 2}) &
IF({Field 3}, "\n• " & {Field 3}) &
...
IF({Field 28}, "\n• " & {Field 28})
), '"', "")