LEFT and RIGHT functions are returning incorrect results

[dksingla]

Description

Below is an example of the LEFT formula

[["0003", "=LEFT(A1,2)"]];

It returns 3, instead, it should return 00 as LEFT takes the first argument of the typed string.

[sequba]

Hi @dksingla thanks for your report. This behaviour is caused by interpreting string 0003 as a number and therefore trimming the leading zeros. To avoid this behaviour you need to start your string literal with ' character (apostrophe) to inform the parser that you don't want it to be converted to number.

Try this:

[["'0003", "=LEFT(A1,2)"]];

I see that this behaviour is not documented in our docs, so I'm creating an issue to do so: #960

Unit tests that demonstrate this:

hyperformula/test/CellContentParser.spec.ts

Lines 128 to 135 in fd0a405

	it('starts with \'', () => {
	expect(cellContentParser.parse('\'123')).toEqual(new CellContent.String('123'))
	expect(cellContentParser.parse('\'=1+1')).toEqual(new CellContent.String('=1+1'))
	expect(cellContentParser.parse('\'\'1')).toEqual(new CellContent.String('\'1'))
	expect(cellContentParser.parse('\' 1')).toEqual(new CellContent.String(' 1'))
	expect(cellContentParser.parse(' \'1')).toEqual(new CellContent.String(' \'1'))
	expect(cellContentParser.parse('\'02-02-2020')).toEqual(new CellContent.String('02-02-2020'))
	})

[dksingla]

Thanks, @sequba for the explanation.

[sequba]

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