I benefited a lot from this post

Concatenate a range of cells with a comma

On top of concatenating, is it possible for me to replace those empty cell with 0 in one line of command?

E.g. Can I get 1,0,4, from the following table in one line of command?

The post above taught me two commands, but they are not helpful. Notice

=ArrayFormula(concatenate(A1:C3&","))

will give 1,,4,

while

=textjoin(",", 1, A1:C3)

will give 1,4,

(I am aware that there are ways to fill blank with zero beforehand, but I have a lot to work with so it wouldn't be efficient.)

Thank you in advance!

CodePudding user response：

Start with

arrayformula(if(isblank(A1:C3),0,A1:C3))

and use concatenate or other functions you need from there

concatenate(arrayformula(if(isblank(A1:C3),0,A1:C3)&","))

gives 1,0,4, for example

CodePudding user response：

Could also try

=substitute(substitute(textjoin(",",false,A1:O1),",,",",0,"),",,",",0,")

or

=substitute(substitute(regexreplace(textjoin(",",false,A1:1),",*$",""),",,",",0,"),",,",",0,")