how to read strings that starts with leading 0s

[makarandp0]

@sequba This question was earlier opened as #1326. Opening this new issue, because I could not figure out how to reopen existing issue as you suggested in your reply.

====
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');
  });
});

We did find this trick about literal ' character, to denote a string 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 based 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.

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

[sequba]

This is the continuation of the discussion from #1326

[sequba]

Ok, depending on your specific use case you might try one of the following ideas:

1. Custom string/number detection before passing value to HyperFormula

function preProcessNamedExpressionValue(inputString) {
  if (inputString.startsWith('=')) {
    return inputString;
  }
  
  const inputAsNumber = Number(inputString);

  if (!Number.isNaN(inputAsNumber) && !inputString.startsWith('0')) {
    return inputAsNumber;
  }

  return `'${inputString}`;
}

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

2. Storing all named expression values as strings and relying on automatic type coersions

  hfInstance.addNamedExpression('myVariable', `'${inputString}`);

If inputString contains a number it would be automatically converted by most functions that expect a number. E.g. formula =ABS(myVariable) would work as if myVariable was a number.

  it('Function ABS should convert strings to numbers', () => {
    const engine = HyperFormula.buildFromArray([
      ["123", "=ABS(A1)"],
      ["'123", "=ABS(A2)"],
    ]);

    expect(engine.getCellValueType(adr('A1'))).toEqual('NUMBER');
    expect(engine.getCellValueType(adr('A2'))).toEqual('STRING');
    expect(engine.getCellValue(adr('B1'))).toEqual(123);
    expect(engine.getCellValue(adr('B2'))).toEqual(123);
  })

Will it work?