how should we read a string with leading 0 (zero)?

[makarandp0]

We use hyperfomula with named expressions. I noticed that if a named expression was a string that could be parsed as a number and had leading zeros, hyperformula always strips out those zeros. I tried few alternatives like concatenating it with another string or such, but did not find a way to extract the string with leading zeros intact.

Can you suggest some way to extract the original string?

describe('hyperformula', () => {
  it('strips leading 0s from string', () => {
    const hfInstance = HyperFormula.buildFromArray([], {
      licenseKey: 'gpl-v3',
    });

    hfInstance.addNamedExpression('myVariable', '02345');

    // looks like hyperformula strips leading 0s from the string if they contained all
    // numbers. how do I extract the variable as string value while preserving leading 0?
    const formula1 = '=IF(myVariable = "02345", "YES", "NO")';
    const result1 = hfInstance.calculateFormula(formula1, 0);
    expect(result1).toStrictEqual('YES');

    // I found this ugly workaround but hoping for something better.
    // const formula2 = '=IF("0" & myVariable = "02345", "YES", "NO")';
    // const result2 = hfInstance.calculateFormula(formula2, 0);
    // expect(result2).toStrictEqual('YES');
  });
});

• HyperFormula version: 2.1.0
• Browser Name and version: N/A
• Operating System: N/A

[adrianszymanski89]

Hi @makarandp0

Thank you for contacting us. I asked our developer to clarify this behavior. I'll update you once I have more information from him.

[sequba]

This behavior is caused by interpreting string 02345 as a number and, therefore trimming the leading zeros. To avoid this behavior you need to start your string literal with the ' character (apostrophe) to inform the parser that you don't want it to be converted to a number.

hfInstance.addNamedExpression('myVariable', "'02345");

This information will soon be included in our documentation: #960

I hope this answer is helpful to you. If not, please re-open the issue.

[makarandp0]

Thank you for your reply @sequba,

I did find this trick about literal ' character. but it does not help in our case. We do not have a control over the input. We know that it must be a string when we evaluate a formula, but we do not know about it when creating a named expression.

I am looking for a formula workaround where we could parse it as string. Typically using CONCATENATE function works for other numeric values to be converted to string, but when it comes to leading 0, those are lost even before CONCATENATE gets a chance to do anything.

[makarandp0]

BTW, I couldn't figure out how to re-open the issue :(

[sequba]

@makarandp0 We've just released the new version of the documentation with a detailed description of how to force a string value type.