2

So I quite often find myself doing tasks on Excel which involve evaluating a text string as an array. Generally speaking I just use this:

Function EVAL(Ref As String)
    EVAL = Evaluate(Ref)
End Function

So the formula will be, for example:

=EVAL("{"&CHAR(34)&SUBSTITUTE(TEXTJOIN(";",TRUE,MID(Index[Industries],2,LEN(Index[Industries])-2)),";",CHAR(34)&";"&CHAR(34))&CHAR(34)&"}")

The cells in this example will have contents like:

;Automotive;Rail;Energy;
;Automotive;Rail;
;Energy;
;Automotive;Aerospace;

(As it happens this is the precise problem I'm stuck on right now, though it has come up in different ways in the past.)

This has worked for me in the past, but I've been running into difficulties lately.

I have come to the conclusion it isn't working because application.evaluate, it turns out, has a character limit of 255. I've seen examples of VBA tricks to bypass this for text strings that are formulas rather than arrays, but copy-pasting those they don't seem to work for when I'm using it to interpret a text string as an array rather than as a formula.

Is there some trick to get this to work? (Or, indeed, is there some alternative method to achieve this altogether?)

Dan Hartas
  • 21
  • 6
  • 7
    Hmm, what about using `Split`? – BigBen Apr 19 '22 at 15:05
  • 1
    ^^^ Why not load the whole into an array then loop that array using split to load another array? Instead of trying to force the use of formula, just use vba directly to do what you want. – Scott Craner Apr 19 '22 at 15:10
  • Can you tell us more about what the desired results are? Also, the opposite is also true, if you want to use formulae, then why use VBA. In this specific case it seems fine to go with either. – JvdV Apr 19 '22 at 15:12
  • My VBA skills are rather limited lol, to answer all of the above points. That EVAL formula is about as sophisticated as I get to be honest, short of copy-pasting from here and other places. If the basic shape of the answer is "code up something specific in VBA" I guess I can do the work since I have to do this sort of thing so often. I just thought there might be a simple trick you guys could readily point me to. – Dan Hartas Apr 19 '22 at 15:18
  • In terms of desired results, in this specific case all I really want to do is wrap it in UNIQUE, but I deal with this sort of data a lot, and ideally I just want a quick-and-dirty way to use array formulas in general with it. – Dan Hartas Apr 19 '22 at 15:22
  • So if it's not about VBA perse, please let us know your version of Excel, give is some markdown sample data with expected results and we can help you get the formulae right. I mean why create your VBA based custom UDF, when standard functions could work too. – JvdV Apr 19 '22 at 15:25
  • I'm on 365, Version 2203. Sample data with expected results doesn't really make sense here as this is an intermediate step in a whole class of problems I encounter frequently. Using the example cell contents I gave above, the expected result is an array on which I can do operations: {"Automotive";"Rail";"Energy";"Automotive";"Rail";"Energy";"Automotive";"Aerospace"} – Dan Hartas Apr 19 '22 at 15:37
  • 3
    `TEXTSPLIT()` or `FILTERXML()` then? – JvdV Apr 19 '22 at 15:44
  • Huh, TEXTSPLIT() appears to be exactly what I need, yeah, but it doesn't seem to be available in my Excel version. FILTERXML() is in there, but I don't know how to use it to achieve the results I want. – Dan Hartas Apr 19 '22 at 15:51

1 Answers1

3

Right, as per my comments, if you are using ms365, you could avoid your workbook to be xlsm just because you need to split values into an array. Make use of what is available with native functions, for example:

enter image description here

Formula in C2:

=TEXTSPLIT(CONCAT(A1:A4),,";",1)

Formula in D2:

=FILTERXML("<t><s>"&SUBSTITUTE(CONCAT(A1:A4),";","</s><s>")&"</s></t>","//s[node()]")

Note 1: As per time of writing you'd need to enable the BETA-channel to gain access to TEXTSPLIT(), and if I recall correctly your version (2203) is allowed to start using this function. Just google how to get access and update your Excel.

Both options can obviously be nested inside the UNIQUE() function.

Note 2: If at any point CONCAT()'s limits are reached (32767 characters, thanks @ScottCraner), maybe you can avoid using that with help of the lambda's helper function REDUCE():

=TEXTSPLIT(REDUCE("",A1:A4,LAMBDA(a,b,a&b)),,";",1)

Note 3: In case you can't update your Excel just yet, and you wonder how to use FILTERXML(), don't mind me refering you to another post I wrote a while back here.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 2
    Thanks so much! I have pestered my admin (successfully, amazingly) to let me have beta access so I can use the TEXTSPLIT solution in future, seeing as I can actually read and understand that, but the FILTERXML solution you provided is working and has broken my immediate roadblock. I really appreciate it, thanks! – Dan Hartas Apr 19 '22 at 16:11
  • @DanHartas, that's great to hear. Enjoy the newest functions! – JvdV Apr 19 '22 at 16:12