google-sheetsgoogle-sheets-formulaimplicit-conversion

LAMBDA( value; value) isn't a identity function


The formula

 =SPLIT("1\10/10\00002.2";"\")

doesn't result equal to

 =MAP(SPLIT("1\10/10\00002.2";"\"); LAMBDA(v; v))

At the former, implicity conversion is inhibited; the latter, it takes place.

Why? Is there a way to control this?


Solution

  • To ensure that the result of the formula returns each part as text, use TO_TEXT:

    =ArrayFormula(MAP(TO_TEXT(SPLIT("1\10/10\00002.2";"\")), LAMBDA(v; v)))
    

    This will return

    A B C
    1 10/10 2.2

    I have tested the above using United States, Uruguay and France as the spreadsheet locale.


    Alternative

    If you need to get 0002.2 instead of 2.2, add a ' before 00002.2. Depending on your specific use case, the most straightforward approach might be to add it manually or use a formula.

    Formula example:

    =MAP(SPLIT(SUBSTITUTE("\" &"1\10/10\00002.2", "\", "\'"),"\"), LAMBDA(v,v))
    
    A B C
    1 10/10 00002.2