Len() func breaking when inside a MakeArray

[Laifsyn]

you can try to switch between Option1 and option2(Already done inside the workbook) and the rundown of my issue should be able to be picturable inside the book, but I'm getting empty arrays which messes up my original work's calculation because it expects them to not exists(A possible solution could be to clean the empty indexes of the said array or maybe ignore these empty indexes, but I'm not sure how to do it)

TLDR:
the Empty Arrays Messes with my calculations, and len function breaks when inside a MakeArray: i.e."MakeArray(...Let(Len()))"

I'm struggling to upload attachment, so I'll just drop the Calculation here below.

Please Login or Register  to view this content.

image_2023-01-03_201139955.png

[FlameRetired]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Since you are new here I have added them for you this time.

Regarding MS-Off version in your profile:

Is your forum profile showing the Excel PRODUCT that you need this request to work with?

If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Thank you ahead of time.

[Laifsyn]

So here's the rundown for my version(?)
Product Information
[Microsoft Logo]
Subscription product
Microsoft 365 Apps for Enterprise

Somewhere down I can see the next information:
Microsoft® Excel® for Microsoft 365 MSO (Version 2211 Build 16.0.15831.20098) 64-bit

[Laifsyn]

So I'm unclear on how should I approach the issue because I'm expecting for it to be able to return an array of each single char of the input string, but for some reason it will just appropriately return me the first char of each string, and the remaining chars would just be like "poof" even though Len() is properly returning a numeric value which somehow is different to when the Indirect Range is static.