Help

Make a line break in a formular give me an error, and can't figure out what I'm doing wrong

Topic Labels: Formulas
2738 5
cancel
Showing results for 
Search instead for 
Did you mean: 

I have a roll-up field that rolls up a bunch of warning signs, which I want to display neatly on top of each other instead of separated with commas.

I got a formula with the following:
SUBSTITUTE({Roll up warning},’,’,’\n’)

I’ll expect it to replace all the commas with line breaks - however it just gives me an #ERROR!.

What am I doing wrong?

5 Replies 5

Try to wrap the Rollup with ARRAYJOIN() to convert it to text.

It kind of works but the ARRAYJOIN() add a comma to the beginning of the text and therefor It creates a line break before the text starts.

I tried REPLACE(ARRAYJOIN({Roll up warning}),1,’ ')
But just gives me an error

Any suggestions how to get that first comma out?

The comma is a separator so should be a ‘ghost’ value before that first comma.

86429fd1106306774f37cc691ba3b24818b9ef0d.png

REPLACE needs 4 parameters:

e86a2724b8ae5f54b915e850c5519bd5e7de2fc7.png

The 4 parameters was the bit I was missing.
Ended up with:
SUBSTITUTE(REPLACE(ARRAYJOIN({Roll up warning}),1,1,’’),’,’,’\n’)

And it works.
Thank you

However what do you mean by i need a ‘Ghost value’ before the first comma?

I meant that if a ARRAYJOIN returns values separated by commas, any comma should have a precedent value. Maybe I should have said should exist (I’m not native english speaker :frowning_face: )