(Help) Excel Returning `0` in Cell, 2D Array expected

I have a 2D Array of data in `A2#`. In three cells outside this range, I have three very simple formulas:

`=LEN(A2)`
`=LEN(A2:E15)`
`=LEN(A2#)`

The first cell evaluates to `4`, the expected result (A2 contains a 4-char word).
The second cell evaluates to a 2D array of all the lengths in the range, with `0`s included for blank cells (expected).
The third cell appears to take a long time to evaluate, but never does. There is no sign that it is calculating, except it doesn't appear to respond (until I press any button). When any button is pressed, the formula immediately evaluates to `0` and proceeds to acknowledge the button I pressed (arrow keys move the cursor, typing a key puts that character in the below cell, etc) but only after I press a button. Otherwise it sits there forever, in the text of the formula, waiting.

I expected a 2D array exactly the size of `A2#`, with lengths listed.
My overarching goal was to turn this 2D array of words into a single column with all words listed, no blanks (there are blanks in the original array).

If you can assist with the problem at hand (or even a bigger problem I am trying to accomplish), please don't hesitate. Thanks!

I always feel a little silly when I solve the issue myself not long after posting, but I was struggling with this for a few hours already.
In any case, I found out something odd. I'm not exactly sure what's happening, so I'll explain what I did and hope someone else understands.

I neglected to mention that immediately to the right of the range I specified, there are a few columns with more dynamic arrays (only 1D this time).
My first step was trying to see how large I could make the selection before it quit calculating, to see if I hit some limit. For clarification, my array is 14 columns by 86681 rows large. It exists at A2:N86682. Again, there is data in dynamic arrays at O2:O86682, P2:P86682, and a couple after that. Here are a few more formulas and their output:

`=LEN(A2:N2)` -> `0`, similar issue as above
`=LEN(A2:M86682)` -> expected 2D array (which took very little time to calculate)
`=LEN(A2:N86682)` -> `0`, same issue as above

I then realized I had the dynamic tables hidden in column O and following. I unhid them, and the issue persisted. I inserted a new column before O, placing it at O and shifting the dynamic arrays to P. All of a sudden, when recalculating `=LEN(A2#)`, it worked like a charm! I was quite surprised. Naturally curious, I deleted column O again, restoring the dynamic tables to be immediately after the 2D array. And to my further shock, I did not experience the original issue. If I did not record my troubles, I would probably feel as if I were gaslighting myself.

Now that all of this has happened, I am inclined to believe that I have found some sort of bug in Excel. As for reproducibility, I honestly have no clue. I personally never want to experience this again.

Thanks for the help! I didn't know of TOCOL until now!

Unfortunately, though, you were right. It won't let me TOCOL the entire array (14x86681). I know from using the count functions that I have 302026 cells with contents. Also, I've made SEQUENCE arrays of this size. However, I assume that because TOCOL is run first, it tries to generate an array that is too large, and I get a `#NUM!` error. Is there a way to run TOCOL with BYROW? or maybe with SCAN? Or is there another way to work on the array bit-by-bit?

Also, TOCOL has a parameter to "Ignore Blanks," but when I set it to 1 it keeps them anyway. What is this feature for?

Also, TOCOL has a parameter to "Ignore Blanks," but when I set it to 1 it keeps them anyway. What is this feature for?

It should really read "Ignore Empty" as it will ignore cells that are totally empty, but not cells that contain ""