arrays google-sheets split insert overwrite

Automatically shift down or insert cells so don't get "Array not expanded, would overwite data" error

I'm making a Google Sheet for the roleplaying game Pathfinder in which I want to be able to copy and paste different monster stat blocks from another site to column A and have each section put into individual cells in column B and other columns using SPLIT, etc. Then eventually I want to be able to add different templates onto that, such as the skeleton template, which automatically alter some of the stats.

But I'm having trouble in that because the stat blocks can vary a lot between monsters Spell-like Abilities (SLA's) can be blank for some monsters and have very many for others. I have an array formula to get all the data between "Spell-Like Abilities" and the next section down, "Statistics," and SPLIT each SLA out in column B. But if there are too many SLA's it gets the overwrite error because the result would go over the sections/formulas below that in column B.

I could just move those lower column B sections down to have enough room but because I don't know how many SLA's any monster might have I don't know how many cells of space I would need to leave open. I could leave as many as say 25 but what if a monster I don't know has 26? Plus I don't want to leave a lot of blank space in column B like that as it would make it harder to read or deal with and would be blank most of the time and useless.

So I have been trying to find a way that the right amount of blank cells would be added in automatically for the SLA part in column B for every monster I paste in column A, shifting the cells below it down accordingly. However, I have not been able to find a formula or script that can do this. I could add a part to the formula to count how many cells are between "Spell-Like Abilities" and "Statistics" and use that to know how much space I need but I can't find a formula that adds cells or shifts down cells.

Edit:Edit: The cell is B21. Right now I have a lot of blank cells below that with STATISTICS in cell B41 and other cells with formulas below that. If I move "STATISTICS" up too high I get the error depending on how many SLA's or spells the monster has. Ideally I want B21 to have no blank cells below it by default so I can have STATISTICS in cell B22 or B23. And then whatever stat block I post in column A I want to push STATISTICS and all cells below it down the necessary amount of cells so that the overwrite error doesn't happen. If the monster has no SLA's it won't move. If it has 15 rows of SLA's then STATISTICS and all cells below it will move down 14 so there are 15 cells (counting B21 itself) to display those SLA's in column B.

Here is my formula in that cell. Edit: updated:

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(JOIN("@", (REGEXREPLACE(REGEXREPLACE((INDIRECT("A"&(ARRAYFORMULA(MIN(IF(REGEXMATCH(A1:A40, "Spell-Like|Spells Known"), ROW(A1:A40), )))+2))):(INDIRECT("A"&(ARRAYFORMULA(MIN(IF(REGEXMATCH(A:A, "TACTICS|STATISTICS"), ROW(A:A), )))-2))),"Known","Known:;"),"Prepared","Prepared:;"))), "@")), ",""—"";"))

There is + and - 2 because the actual SLA's have a blank space above and below them in the section.

Here is an example of what it comes out to with Statistics below.

| Constant | protection from good |

| 1/day | shadow walk |

STATISTICS

Here is the sheet: https://docs.google.com/spreadsheets/d/1R8LYW2HGhpM3TS9m4ZBaNstwzNtsyll9fZnQ8WaA3Nc/edit#gid=0

Solution

The original formulas are complicated so I just answer with a simple example. Please modify it yourself.

=ArrayFormula(SPLIT({ INDIRECT("A"&MATCH("Section A",A:A,0)&":A"&MATCH("Section B",A:A,0)-2); "(blank)"; INDIRECT("A"&MATCH("Section B",A:A,0)&":A"&MATCH("Section C",A:A,0)-2); "(blank)"; INDIRECT("A"&MATCH("Section C",A:A,0)&":A"&MATCH("Section C",A:A,0)+2) },","))

Perform different split on each array

=ARRAYFORMULA(SPLIT({A76;A77},IF(REGEXMATCH({A76;A77},";"),";",",")))