[Bug]: in-built function SUBSTITUTE does not work as expected

[makarandp0]

Description

I am looking for a way to convert values that end in decimal point to number. For example I would like "13." to be converted to 13

issue 1.

in excel NUMBERVALUE function does the job. NUMBERVALUE("13.") results in 13
But I did not see it as one of the supported built-in functions. So I thought perhaps i could use SUBSTITUTE, and then run into this bug

issue 2.

import { HyperFormula } from 'hyperformula';
describe('hyperformula', () => {
  it('does not substitute correctly', () => {
    const hfInstance = HyperFormula.buildFromArray([], {
      licenseKey: 'gpl-v3',
    });

    const goodFormula = '=SUBSTITUTE("13x", "x", "x00")';

    const result1 = hfInstance.calculateFormula(goodFormula, 0);
    expect(result1).toStrictEqual('13x00');

    // in the formula below I was expecting it to behave
    // just like the one above, but it doesn't.
    // it returns ".00.00.00"
    const badFormula = '=SUBSTITUTE("13.", ".", ".00")';
    const result2 = hfInstance.calculateFormula(badFormula, 0);
    expect(result2).toStrictEqual('13.00');
  });
});

Video or screenshots

No response

Demo

I have added test code above to reproduce.

HyperFormula version

2.4.0

Your framework

plain javascript.

Your environment

Node v16.18.0, MacOS 12.5

[adrianszymanski89]

Hi @makarandp0

Thank you for reporting this. I was able to recreate the issue on my side: https://jsfiddle.net/handsoncode/m1u7rnok/

This function indeed works for every other sign than ..

@sequba Can you please take a look at this?

[sequba]

@adrianszymanski89 Our implementation of SUBSTITUTE uses RegExp to search for text patterns. That is what causes this weird result.

This behavior is neither compliant with the standard nor consistent with other popular software, so must be considered a bug. We will work on it according to our priorities.

@makarandp0 As a workaround you may escape the special regexp symbols in the search pattern with double backslash (\\)

=SUBSTITUTE("13.", "\\.", ".00")

[AMBudnik]

Hi @makarandp0

Thank you for reporting the issue! I have good news. We just fixed it and released it under HyperFormula v 2.6.1.
You can read mire about the changes for this version at https://github.com/handsontable/hyperformula/releases/tag/2.6.1