Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 22, 2022

long texts with arrays in conditional functions

PeterBartholomew1 , tboulden or someone else Perhaps you know the workaround, even with lambdas. In simplest case   =IF( 1, REPT("a", 256) ) works =IF( {1}, REPT("a", 256) ) returns #VALUE! ...
Formulas and Functions
PCaskey's avatar
PCaskey
Copper Contributor
Jul 08, 2022

SergeiBaklan 
A simple workaround I came up with (maybe some others have, too):

 

If({condition},Str1,Str2)

(Str1, Str2 are arrays)

 

Yields errors for strings > 255 length. Alternative:

 

Left(Str1,Len(Str1)*({condition})) & Left(Str2,Len(Str2)*(1-{condition}))

 

Similar concept can be adapted to other conditional situations.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 08, 2022

PCaskey 

Good idea, but not easy on practice. Especially if we use other conditional formulae, like

=CHOOSE( {7,2,5,3,9,1}, ....

  • lori_m's avatar
    lori_m
    Iron Contributor
    Jul 12, 2022

    SergeiBaklan 

    Interesting -

    I think the earlier idea of mtarler to use INDEX may work depending on context

     

    A) IF({condition},str1,str2)

    maybe one of:

    =INDEX((str1,str2),,,1+{condition})
    =INDEX(HSTACK(str1,str2),1+{condition})

    (first assumes cell references)

     

    B) CHOOSE({7,2,5,3,9,1},col1,col2,...)

    maybe instead:

    =INDEX((col1,col2,...),SEQUENCE(ROWS(col1)),,{7,2,5,3,9,1})
    =CHOOSECOLS(HSTACK(...),{7,2,5,3,9,1})

    (first assumes column ranges)

    • mtarler's avatar
      mtarler
      Silver Contributor
      Jul 12, 2022
      I think the problem with using INDEX was that although it could work (i.e. not get the error because the text string was >255) it would return 0 for blank cells. I believe a lot of the above was about doing both, working with text cells >255 AND returning blank for blank cells. As for using HSTACK, I don't have beta so I can't try that.
      • lori_m's avatar
        lori_m
        Iron Contributor
        Jul 12, 2022

        mtarler 

        Yes, I see that zeroes are an issue with IF when first argument contains arrays but I believe INDEX returns a blank reference like FILTER does within arrays.

         

        If 'blank' refers to a blank cell the following formulas return FALSE, TRUE, TRUE respectively:

        =ISBLANK(IF({1},blank))

        =ISBLANK(FILTER(blank,{1}))

        =ISBLANK(INDEX(blank,{1})

         

        so to replace the blanks with "" one can apply similar logic as SergeiBaklan above,

        =SUBSTITUTE(INDEX(range,indices),,)

        =INDEX(range,indices)&""

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 08, 2022
    So interestingly I used PCaskey idea to create what I believe is a 3rd solution. Here are the 3 "working" solutions I see in this thread:
    =SUBSTITUTE( FILTER( {dataSet}, {dataSet condition} ), "", "" )
    =MAP( FILTER( {dataSet}, {dataSet condition} ), LAMBDA(v, IF( v = "", "", v ) ) )
    =FILTER(LEFT( {dataSet}, LEN({dataSet}) ), {condition})

    note that the MAP-FILTER solution is the only one that preserves a number as a number.

Resources