Why doesn’t the join function work when a range is included in the formula?
=ArrayFormula(JOIN(" | "; TRANSPOR(SPLIT(B2:B; " | ";0)) & " " & TRANSPOR(SPLIT(A2:A; " | "))))
The error "The value of SPLIT Function parameter 1 needs to be filled in."
| Data | Cod | With interval | Without the break |
|---|---|---|---|
| A | B | F | 1 | 2 | 6 | #VALUE! | 1 A | 2 B | 6 F |
| A | B | F | 1 | 2 | 6 | ||
| A | 1 | ||
| B | 2 | ||
| C | 3 | ||
| D | E | R | 4 | 5 | 18 |
Below is the simulation spreadsheet:
Test Join
What would be an alternative to get around this?
>Solution :
You’re combining two conditions for the ARRAYFORMULA. I understand you want to process the whole column row by row, and apply it to each of the splitted value inside each cell. So you should need another auxiliary formula to get an order of processing: if you want to get the whole range row by row, I’d suggest to use MAP:
=MAP(A2:A;B2:B;LAMBDA(aCell;bCell;IF(aCell="";"";
ARRAYFORMULA(JOIN(" | "; TRANSPOSE(SPLIT(bCell; " | ";0)) & " " & TRANSPOSE(SPLIT(aCell; " | ")))))))
