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


#1

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?


#2

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


#3

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?


#4

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

REPLACE needs 4 parameters:


#5

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?


#6

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:)