Spread sheet formula doesn't calculate the results with colon(:)

Posted by: ashok.purty on 7 April 2020, 11:47 pm EST

  • Posted 7 April 2020, 11:47 pm EST

    Spread sheet formula doesn't calculate the results with colon(:) if user inputs the formula manual using keyboard, but works fine in the case of copy paste.

    We are using some custom culture setting like :

    // Sample code
    const myCulture = new GC.Spread.Common.CultureInfo();
    myCulture.NumberFormat.numberDecimalSeparator = decimalSeparator;
    myCulture.NumberFormat.numberGroupSeparator = thousandSeparator;
    myCulture.NumberFormat.currencyDecimalSeparator = decimalSeparator;
    myCulture.NumberFormat.listSeparator = ';';
    myCulture.NumberFormat.arrayListSeparator = ':';
    //

    so the format =sum(k5:l5) throws InvalidOperation error if user inputs value using keyword(cell edit mode), but the same formula when 1st written in notepad and paste directly in the cell work fine and execute the formula and renden in the value in spread sheet cell.

    If it is working fine in copy paste operation then same behaviour should be in the case of cell edit mode as both should use the custom culture.

    Could you please let us know how ':' is working fine in case of copy paste so that we can config same for cell edit mode as well.
  • Replied 12 April 2020, 9:17 pm EST

    Hi Ashok,

    The issue is arising because a different symbol is used for list separator and arrayListSeparator. Using the same symbol should the fix. Further, I would recommend you not to use ":" as the listSeparator symbol because it is a reserved symbol in excel for range references.

    Regards
    Sharad
  • Replied 13 April 2020, 10:40 pm EST

    Hi Sharad,
    I am getting mentioned error after implementing your suggested fix.
    "The issue is arising because a different symbol is used for list separator and arrayListSeparator. Using the same symbol should the fix."

    Error :
    numberDecimalSeparator, listSeparator and arrayListSeparator should be different in cluture info.

    Also could you please let me know, how it's working fine in case of copy paste ?
  • Replied 14 April 2020, 11:53 pm EST

    Hi Ashok,

    In our investigation we found that the error is thrown only if listSeparator = arrayListSeparator = ";" other symbols are working fine. So we have forwarded it to the concerned team for further investigation of the issue(Internal tracking ID: SJS-4135). We will let you know about any updates regarding the same.
    Till then if it is possible please use the custom symbols such that listSeparator = arrayListSeparator = "some symbol other than ;" != numberDecimalSeparator.
  • Replied 16 April 2020, 4:37 pm EST

    Thanks for update :-)
    I will try if your suggestion e.g "some symbol other than ;" will work with my current implementation.
    Please keep me posting from your team on investigation of this issue.

    Thanks,
    Ashok Purty
  • Replied 20 April 2020, 8:26 pm EST

    Hi Ashok,

    Dev team has informed us that ":" including some other characters are reserved for calcEngine which is causing the issue.
    Further, for a better understanding, the team has asked for additional information regarding the use case that why do you want to set a culture using ":" and ";", if you are trying to custom culture for some language, could you please inform us about the language too?

    Regards
  • Replied 21 April 2020, 11:33 pm EST

    Hi Sharad,
    But one question is still open
    why this is working fine with copy paste operation without edit mode of the cell ??
    Could please update me on this ??

    More information about setting culture:
    We are setting custom culture for finish language. And as in finish language ',' is considered as decimal separator, that is
    why we configured ',' as decimal separator and ';' as list separator.

  • Replied 22 April 2020, 7:34 pm EST

    Hi Ashok,

    Thank you for providing the additional details. We have shared it with the dev team.
    >> why this is working fine with copy paste operation without edit mode of the cell ??
    It seems that the issue is arising because, formula text box performs some additional operations to support reference highlighting, formula autocomplete etc which is modifying the actual formula. Check the formula after committing the edit value and notice that ":" is removed from the formula. Further, we have also asked the dev team for the actual cause of this issue and will let you know about their response.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels