Convert A Long String To An Array, With Function Restrictions

[mrblister]

Hello all,

I need a formula to convert a very long string into an array (delimiter is comma (",")).

When the string is converted into an array, the string segments are long (43 characters each). This causes issues with my current formula, because if there are over ~50 elements (string segments) in the array then my current formula fails. I believe the failure is due to the combination of REPT and SUBSTITUTE in my array formula creating an intermediary string that is larger than Excel's string size limits.

The string in A1 is in the format: "HELLOHOWAREYOU.IAMFINE.ITCOSTS$34.000.00012,HELLOHOWAREYOU.IAMFINE.ITCOSTS$34.000.00085,HELLOHOWAREYOU.IAMFINE.ITCOSTS$34.000.00129..."

My current formula in B1 is:

=LET(the_array,A1,num_items,LEN(the_array)-LEN(SUBSTITUTE(the_array,",",""))+1,TRIM(MID(SUBSTITUTE(the_array,",",REPT(" ",LEN(the_array))),(ROW(($A$1):INDEX($A:$A,num_items))-1)*LEN(the_array)+1,LEN(the_array))))

The formula creates an array in column B:
B1="HELLOHOWAREYOU.IAMFINE.ITCOSTS$34.000.00012"
B2="HELLOHOWAREYOU.IAMFINE.ITCOSTS$34.000.00085"
B3="HELLOHOWAREYOU.IAMFINE.ITCOSTS$34.000.00129"
... and so on. The strings are just examples. But there are special characters and numbers, but the string segments are all the same size (43 characters long).

NOTE: The system where this formula will be used, doesn't have access to the following functions: MAKEARRAY, TEXTSPLIT, FILTERXML.

Can someone please assist me in either replacing my current formula, or repairing the current formula so that it can handle larger text strings?

Thanks in advance!

 

[Peter_SSs]

Would this be any use?

=LET(the_array,A1,num_items,LEN(the_array)-LEN(SUBSTITUTE(the_array,",",""))+1,MID(the_array,SEQUENCE(num_items,,,44),43))

.. or without the names within LET, just

=MID(A1,SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1,,,44),43)

 

[mrblister]

Yes, thank you for your help! Both formulas are great and solve my problem. Cheers!

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.