Please give me an ArrayFormula for this formula

user_8866238 0
Viewed 134

I want to achieve column I.

Formula is:
=textjoin(char(10),false,A4:F4)

When I convert to Arrayfromula:
=transpose(split(textjoin(char(10),false,{ArrayFormula("~"&A4:A),A4:F}),"~"))

enter image description here Editable Sheet link

In my Arrayformula the first value is repeating, Please Help!

EDIT:

Is there any way to achieve Arrayformula 1 (two columns) & Arrayformula 2 (single column)?

Numbers sheet:
(Basically not include cells with value 0) enter image description here

The formula should even work with Strings as data not compulsorily Numbers as shown below:

Strings & Numbers sheet:
(Basically not include cells with value 0 & NULL)
enter image description here

array-formulas google-sheets-formula google-sheets line-breaks
1 Answers

try:

=ARRAYFORMULA(REGEXREPLACE(REGEXREPLACE(FLATTEN(QUERY(TRANSPOSE(
 TO_TEXT(A4:F6)&"×"),,9^9)), "×", CHAR(10)), " ", ))

enter image description here

update:

=ARRAYFORMULA({IF(TRIM(FLATTEN(QUERY(TRANSPOSE(A4:F),,
 9^9)))="",,JOIN(CHAR(10), A1:F1)), 
 REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(FLATTEN(QUERY(TRANSPOSE(
 TO_TEXT(SUBSTITUTE(A4:F, " ", "♀"))&"×"),,
 9^9)), "×", CHAR(10)), " |\n$", ), "♀", " ")})

enter image description here

=ARRAYFORMULA(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(A4:F),,9^9)))="",,
 REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(FLATTEN(QUERY(TRANSPOSE(
 TO_TEXT(SUBSTITUTE(IF(A4:F="",,A1:F1&": "&A4:F), " ", "♀"))&"×"),,
 9^9)), "×", CHAR(10)), " |\n$", ), "♀", " ")))

enter image description here

user_56326290
Related