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.

 

[mrblister]

Hello All,

The above solution (from @Peter_SSs) had worked great for me. However, I'm now dealing with a very long string, which the string segments are now variable-length. I can't use the above formula anymore since it's for fixed-length string segments.

So for example, if the string in A1 is in the format: "0.1,42.3,76.31..."

The formula needs to create an array in column B:
B1="0.1"
B2="42.3"
B3="76.31"
... and so on

And again the formula must be more efficient than the one in my original post, as it fails if there are more than ~50 string elements in the input string.

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

Can anyone assist? @Peter_SSs?

 

[Fluff]

How about

=LET(a,","&A2&",",s,SEQUENCE(LEN(a)-LEN(SUBSTITUTE(a,",",""))-1),REPLACE(LEFT(a,FIND("^",SUBSTITUTE(a,",","^",s+1))-1),1,FIND("^",SUBSTITUTE(a,",","^",s)),""))

 

[Peter_SSs]

.. a slightly abridged version

=LET(a,","&A2&",",s,SEQUENCE(LEN(a)-LEN(SUBSTITUTE(a,",",""))-1),p,FIND("^",SUBSTITUTE(a,",","^",s)),REPLACE(LEFT(a,FIND(",",a,p+1)-1),1,p,""))

The system where this formula will be used, doesn't have access to ... FILTERXML.

Does that mean you are working on a Mac?
.. or just a much older Excel version than your profile shows?

 

[mrblister]

@Fluff and @Peter_SSs, thank you both very much. It seems to work great!

Does that mean you are working on a Mac?
.. or just a much older Excel version than your profile shows?

I actually use 2021 (hoping to upgrade to 2024 soon). But the target system where the formulas will be used, however, is... quite ancient.

 

[Peter_SSs]

Hmm, that is all slightly confusing.

the target system where the formulas will be used, however, is... quite ancient.

1. "Ancient system" Does that mean ancient hardware or ancient Excel?
If ancient Excel then the above formulas will not work.

2. If you are not using a Mac and are using Excel 2021 (or anything from Excel 2013 I think) then you should have FILTERXML (though it may not spill the results, depending on the Excel version).

25 02 22.xlsm
	A	B	C
1		Spill	No Spill
2	0.1,42.3,76.31,0.32	0.1	0.1
3		42.3	42.3
4		76.31	76.31
5		0.32	0.32
6
7
mrblister

Cell Formulas
Range	Formula
B2:B5	B2	=FILTERXML("<p><c>"&SUBSTITUTE(A2,",","</c><c>")&"</c></p>","//c")
C2:C7	C2	=IFERROR(INDEX(FILTERXML("<p><c>"&SUBSTITUTE(A$2,",","</c><c>")&"</c></p>","//c"),ROWS(C$2:C2)),"")
Dynamic array formulas.

3. If you do not have FILTERXML then presumably you also will not have LET, SEQUENCE etc as used in our suggestions above. You also would not be able to get a spilled array with a single formula. In that case you might have to use something like one of these (depending on whether you want numerical or text results), copied down.

25 02 22.xlsm
	A	B	C
1		Numerical	Text
2	0.1,42.3,76.31,0.32	0.1	0.1
3		42.3	42.3
4		76.31	76.31
5		0.32	0.32
6
7
mrblister (2)

Cell Formulas
Range	Formula
B2:B7	B2	=IFERROR(--RIGHT(SUBSTITUTE(LEFT(A$2,FIND("#",SUBSTITUTE(A$2&",",",","#",ROWS(B$2:B2)))-1),",",REPT(" ",20)),20),"")
C2:C7	C2	=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A$2,FIND("#",SUBSTITUTE(A$2&",",",","#",ROWS(B$2:B2)))-1),",",REPT(" ",20)),20)),"")