Identical Excel formula work on one sheet but no another

[Kay_Engineer]

Hi I am working on a spreadsheet from my colleague and I would like to use the & function or the CONCAT() function however it is not working on the spreadsheet they left me, however, when I have created another spreadsheet myself, or another page, the formula works, may I ask if any one knows what makes the equation not working on one page but another. I have checking the cell format, the setting look the same to me.

formula that works on both pages:
=CONCAT("excel ",C9)

formula that only works on the page I newly created:
="excel "&C9

=CONCAT("excel ", C9+1)

[#2]

Could it be that you have like an error in cell C9. What happens if you clear that cell, or enter a string?

[#3]

https://1drv.ms/x/s!ApRAKxWiqaZBhJ5VdLQZ9ltrEU85Hg?e=wMTZn8

The cell C9 is only a number, 1, not any type of calculation

[#4]

I cannot access the file. Is it same result also if you change from C9 to another cell, eg an empty cell that you then enter 1 into?
Normally, it should be that the result in C9 cannot be treated as a string.
The result from the four tests?

[#5 · Accepted solution]

It is fixed now, it is a setting problem, please check the solution here in case anyone needs it.
https://www.excelforum.com/excel-formulas-and-functions/1399559-identical-excel-formula-work-on-one-...

[#6]

interesting. I couldn't find that setting on the worksheet, you must get to it using desktop app?
FYI the article says:
You have a Lotus 1-2-3 compatibility option checked for that sheet that is interfering with formula evaluation. Go to File, Options, Advanced, and uncheck the "Transition formula evaluation" option.

[#7]

@Kay_Engineer 

An interesting thing happens in the sheet's xml with that setting enabled:

[#8]

So, Excel still concatenates if it is possible to convert the cells result/value to a text/string, being not possible with an error.
Thanks - I did not really expect such setting to be of weight today.