if-statement google-sheets google-sheets-formula array-formulas google-sheets-query

Concat in Arrayformula using row numbers

UPDATE - Modified answer

With help from @player0, found solution that handled line seperators too:

=ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(SPLIT(TRANSPOSE(SPLIT(QUERY( IF(D1:D="[task]", "♦"&D1:D, "♣♀"&D1:D),,999^99), "♦", )), "♀")),,999^99)),"\s*♣\s*",char(10)))

This moves each [task] section into its own cell, retaining all other formatting, and stops the extra whitespace that gets added around each cells content that are joined/concat together.

Original Post

Can't seem to find anywhere else this is done, so happy for any links in comments too :)

I have a column of text, the contents of over a hundred files with [task] at top of each.

More than 50,000 characters, so they pasted into a newline each cell ('paste as values').

I can easily tell the row numbers to work with and have a draggable working formula, just can't get it properly ARRAYFORMULA capable, since the input might change by easily a thousand rows so needs to handle that gracefully.

Formulas only please, no code

Example Spreadsheet https://docs.google.com/spreadsheets/d/1yaETa3Pvq47DpcAyojSlf6p5N1Tt_hpvoqp7WEtC6pI/edit?usp=sharing

Example Input (50,000+ characters) https://drive.google.com/file/d/1sMl0ZQfbXZUjpB0fic2VkX5Ze5H_IEGL/view?usp=sharing

Note - if the input file is removed when I clean my drive up, just copy-paste the sample below till 50,000+ characters, or see linked spreadsheet

Input

[task]
SpriteNE = floor_metal_01.dds
SpriteSE = floor_metal_01.dds
SpriteSW = floor_metal_01.dds
SpriteNW = floor_metal_01.dds
guilevel = 1
guiindex = 10
type = COMPONENT
locked = 0
name = category_advanced_manufacturing
parent

[task]
SpriteNE = floor_metal_01.dds
SpriteSE = floor_metal_01.dds
SpriteSW = floor_metal_01.dds
SpriteNW = floor_metal_01.dds
guilevel = 1
guiindex = 9
type = COMPONENT
locked = 0
name = category_facilities
parent

[task]
tilelable
SpriteNE = conveyor_NE.dds
SpriteSE = conveyor_SE.dds
SpriteSW = conveyor_SW.dds
SpriteNW = conveyor_NW.dds
exitdirectionoffsets = 0
inputdirectionoffsets = 2
guilevel = 1
parent
guiindex = 0.5
name = task_conveyor
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_SE.dds

[task]
tilelable
SpriteNE = conveyor_converge3_NE.dds
SpriteSE = conveyor_converge3_SE.dds
SpriteSW = conveyor_converge3_SW.dds
SpriteNW = conveyor_converge3_NW.dds
exitdirectionoffsets = 0,
inputdirectionoffsets = 1,2,3
guilevel = -1
parent
guiindex = 1
name = task_conveyor_converge3
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_converge3_SE.dds

[task]
tilelable
SpriteNE = conveyor_crossover_NE.dds
SpriteSE = conveyor_crossover_SE.dds
SpriteSW = conveyor_crossover_SW.dds
SpriteNW = conveyor_crossover_NW.dds
exitdirectionoffsets = 0,3
inputdirectionoffsets = 1,2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_crossover
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_crossover_SE.dds

[task]
tilelable
SpriteNE = conveyor_left_NE.dds
SpriteSE = conveyor_left_SE.dds
SpriteSW = conveyor_left_SW.dds
SpriteNW = conveyor_left_NW.dds
exitdirectionoffsets = 3
inputdirectionoffsets = 2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_left
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_left_SE.dds

[task]
tilelable
SpriteNE = conveyor_right_NE.dds
SpriteSE = conveyor_right_SE.dds
SpriteSW = conveyor_right_SW.dds
SpriteNW = conveyor_right_NW.dds
exitdirectionoffsets = 1
inputdirectionoffsets = 2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_right
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_right_SE.dds

[task]
tilelable
SpriteNE = conveyor_split3_NE.dds
SpriteSE = conveyor_split3_SE.dds
SpriteSW = conveyor_split3_SW.dds
SpriteNW = conveyor_split3_NW.dds
exitdirectionoffsets = 0,1,3
inputdirectionoffsets = 2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_split3
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_split3_SE.dds

[task]
tilelable
SpriteNE = conveyor_t_NE.dds
SpriteSE = conveyor_t_SE.dds
SpriteSW = conveyor_t_SW.dds
SpriteNW = conveyor_t_NW.dds
exitdirectionoffsets = 3,1
inputdirectionoffsets = 2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_t
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_t_SE.dds

[task]
tilelable
SpriteNE = conveyor_t_join_NE.dds
SpriteSE = conveyor_t_join_SE.dds
SpriteSW = conveyor_t_join_SW.dds
SpriteNW = conveyor_t_join_NW.dds
exitdirectionoffsets = 0
inputdirectionoffsets = 1,3
guilevel = -1
parent
guiindex = 1
name = task_conveyor_t_join
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_t_join_SE.dds

[task]
tilelable
SpriteNE = conveyor_t_join_left_NE.dds
SpriteSE = conveyor_t_join_left_SE.dds
SpriteSW = conveyor_t_join_left_SW.dds
SpriteNW = conveyor_t_join_left_NW.dds
exitdirectionoffsets = 0
inputdirectionoffsets = 2,3
guilevel = -1
parent
guiindex = 1
name = task_conveyor_t_join_left
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_t_join_left_SE.dds

[task]
tilelable
SpriteNE = conveyor_t_join_right_NE.dds
SpriteSE = conveyor_t_join_right_SE.dds
SpriteSW = conveyor_t_join_right_SW.dds
SpriteNW = conveyor_t_join_right_NW.dds
exitdirectionoffsets = 0
inputdirectionoffsets = 1,2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_t_join_right
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_t_join_right_SE.dds

[task]
tilelable
SpriteNE = conveyor_t_left_NE.dds
SpriteSE = conveyor_t_left_SE.dds
SpriteSW = conveyor_t_left_SW.dds
SpriteNW = conveyor_t_left_NW.dds
exitdirectionoffsets = 0,3
inputdirectionoffsets = 2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_t_left
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_t_left_SE.dds

[task]
tilelable
SpriteNE = conveyor_t_right_NE.dds
SpriteSE = conveyor_t_right_SE.dds
SpriteSW = conveyor_t_right_SW.dds
SpriteNW = conveyor_t_right_NW.dds
exitdirectionoffsets = 0,1
inputdirectionoffsets = 2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_t_right
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_t_right_SE.dds

Desired Output

Each [task].* is a new file, so seperate by those into one cell each in a single column, with newlines (\n) as internal separators.

In other words, everything after [task] till the next [task] (or end of column, if no more [task]) combined into one cell each.

Just has to be arrayformula able and avoid the char limit for functions like concat of 50,000 characters.

Current Output

Getting row numbers

$D$1:D is the input column

$G$1 is dropdown with "[task]" selected

$M$1 =ARRAYFORMULA(QUERY({$D1:$D, row($D1:$D)},"select * where Col1 = '"&$G$1&"'"))

$O$1 =ARRAYFORMULA(QUERY({$M1:$M,{$M2:$M;0}},"select *"))

Then some of my attempts to use these numbers

Works, has to be dragged through, and doesn't have any separators.

=ARRAYFORMULA(CONCATENATE(INDIRECT("$D"&$O1:$O&":$D"&$P1:$P-1)))

Doesn't work, I kept adding more arrayformula but still just first result

=ARRAYFORMULA(IF($P1:P<>"",CONCATENATE(ARRAYFORMULA(INDIRECT("$D"&ARRAYFORMULA(INDIRECT("$O"&IF($P1:$P<>"",row($P1:$P),"")&":$O"))&":$D"&ARRAYFORMULA(INDIRECT("$P"&IF($P1:$P<>"",row($P1:$P),"")&":$P"))-1))),""))

Just the string used in previous INDIRECT

=ARRAYFORMULA(IF($O1:O<>"","$D"&ARRAYFORMULA(INDIRECT("$O"&IF($P1:$P<>"",row($P1:$P),"")&":$O"))&":$D"&ARRAYFORMULA(INDIRECT("$P"&IF($P1:$P<>"",row($P1:$P),"")&":$P"))-1,""))

Doesn't work, has separators but still only looping the first result.

$U1:U is the string list for indirects (see above)

=ARRAYFORMULA(IF($U1:U<>"",TEXTJOIN(";",FALSE,INDIRECT($U1:U)),""))

There are some other formulas in the example sheet, I mostly removed the duds as I kept going though.

I found lots of interesting resources out there but nothing that seemed to target using row numbers, or even just SPLIT + JOIN type approach that allowed ARRAYFORMULA (AND avoided the 50,000 character limit issue with single CONCAT or similar calls).

Solution

try:

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(QUERY(
 IF(A2:A="[task]", "♦"&A2:A, "♀"&A2:A),,999^99), "♦", )), "♀"))