Problems editing .xls file in calc

[RodWard]

A cell (fomat=text) references two other text cells in another sheet and concatenates them as follows:-

=CONCATENATE($‘FILL IN HERE’.B4,":",$‘FILL IN HERE’.A4)

Both the referenced cells are text but the result is displayed as 0. Why is this?
If I use the function wizard, it shows the result as the concatenated text strings I expect.

To get round this I tried to use two cells referencing one other cell. I enter ‘=’ and then click on the desired cell in its own sheet, return to the edited cell and save it. I just get the text =$‘FILL IN HERE’.B4 not the referenced cell.

As I have other cells referencing cells in the other sheet I copied one and pasted it back in the row. The target cell was updated correctly in the edited cell from D to B but what was shown was the value from the copied cell D.

I just do not understand what is happening.

Any ideas very welcome.
Rod

Update.
Having played around with this spreadsheet in both XLS and ODS formats, I have come to the conclusion that this particular spreadsheet is corrupt. I have the same or similar problem with it in either format and I cannot reproduce the problem in other spreadsheets. Luckily it is not a major problem and does not interfere with the main purpose of the spreadsheet. The reason I reinstalled LibreOffice was that the previous version stopped working and would not load. It may have first failed on this spreadsheet.
Consider this solved. Thanks for the answers,
Rod

[LeroyG]

@RodWard,

Formulas written on cells formatted as Text will only be showed as text. Change it to Number instead. Can’t understand why the result is 0.

Edit your question if you want to add more information; also can comment an answer. Do not use Add Answer to comment.

[RodWard]

I want the answer to be text. Both the referenced cells and receiving cell are text. The 0 is left aligned. If I change the receiving cell to Number the 0 is Right aligned. If I change it to Date I get ‘30 Dec 99’. Function Wizard shows the correct text string but only 0 is displayed.

[LeroyG]

Calc and Excel are not 100% compatible. This said…

If you assign “@” (Text) as Category to a cell, and then you write a formula in that cell, the formula will be shown as a text string, and Calc will not process it as formula.

If you assign “Number” as Category to a cell, and then you write a formula in that cell, the formula result will be shown. If afterward you change the Category to “@” (Text) without editig the formula, it will continue to show the answer.

So, change the Category to “Number”, edit the cell (F2), add and delete a space (or whatever character) and press Enter. And save as .ods.

Edit your question if you want to add more information; also can comment an answer. Do not use Add Answer to comment.

Check the mark () to the left of the answer that solves your question.

[RodWard]

Having played around with this spreadsheet in both XLS and ODS formats, I have come to the conclusion that this particular spreadsheet is corrupt. I have the same or similar problem with it in either format and I cannot reproduce the problem in other spreadsheets. Luckily it is not a major problem and does not interfere with the main purpose of the spreadsheet. The reason I reinstalled LibreOffice was that the previous version stopped working and would not load. It may have first failed on this spreadsheet.

Consider this solved. Thanks for the answers,
Rod