Formula is displayed in cell instead of result

English
#1

I have posted about this topic some years back

One of the very, very most frustrating events or actions in LibreCalc is when a formula is displayed in a cell instead of the result! There seems to be no way to prevent or eliminate this action short of retyping the formula: not an acceptable solution when there are several such instances.

Is there anything that can be done to prevent or eliminate this problem?

The only method I’ve found to eliminate the problem is to copy the spreadsheet and open it on a Mac with Excel 4.0. Then, I reopen in LibreCalc. The problem does not recur until I edit a potentially offending cell. It occurs again.

#4

Is there an apostrophe at the left of the formula?

All formulas are shown “as formula”?

Are you saving as .ods?
Can you share a file to test, indicating a offending cell? Thanks.
Can you share the version information? (menu Help - About LibreOffice, use the button there to copy, and paste here)

2 Likes
#6

Was the file created in LibreOffice and saved as an .ods file?

#7

It sounds like an Excel 4.0 macro which predates VBA. The result is placed into the cell by Excel which LibreOffice leaves untouuched because it doesn’t understand it. I would not support LibreOffice running those, even Microsoft says that they should be converted.

#8

From “LibreOffice 7.4 Help,” module “Calc” subsection “Number Format Codes,” item 4 states “4. Fourth section applies if the content is not a value, but some text. Content is represented by an at sign (@).” It is to be noted that “Content,” not “Formula” is referred.

From a subsection of “Number Format Codes” titled “Text and Text,” it is stated “To include text in a number format that is applied to a cell that might contain text, enclose the text by double quotation marks (” "), and then add an at sign (@). For example, enter "Total for “@ to display “Total for December”.”

There is nothing in subsection “Text and Text,” or the whole section “Number Format Codes,” discussion that states to the effect that using the @ format will cause formulas to be displayed as text instead of results. The whole section “Number Format Codes” refers only to number and text result formats. It does not discuss FORMULA formats. There is no warning that preformatted a cell with @ will cause the display of the FORMULA instead of RESULTS of the FORMULA! Thus, according to LibreOffice, using the @ sign is a valid formatting code.

I begin my spreadsheets with a template in which all cells are formatted “General.” Subsequently, and as needed, I use many number (-#0.0) and text (@) formats in cells. I work in my spreadsheets according to LibreOffice 7.4 Help “Number Format Codes” information.

Attached is “Example.ods.” It was created in LibreOffice Calc with all cells being preformatted “General.” Then, after entering the formulas, I changed formatting as desired and in accord with the “LibreOffice 7.4 Help” instruction files.

To replicate my problem, select the equal sign at the beginning of the formula in cell F16 and type an equal sign. If, as in my versions of LibreOffice Calc, the cell will immediately display the formula instead of the result. There is no way, short of retyping the formula, that will restore the result instead of formula display.

Notes:

I really do not understand how Ask LibreOffice forum web page works. I am attempting now to determine how to login. If I have posted this reply in an incorrect manner, I apologize.

LeroyG & schiavinatto: Experience indicates it does not matter whether the file was created in LibreOffice or in Excel.

EarnestAl: There is no connection to macros involved!

Last, is there a way I can prevent responses to this post being sent to my email. I receive html or other coding symbols mixed in with the message. It’s garbled, and for all practical purposes, unreadable text.

Version information:
Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 2; OS: Linux 6.1; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Debian package version: 4:7.4.7-1+deb12u10
Calc: threaded
Example.xls (10.5 KB)

#9

image

image
image751×452 34.5 KB

1 Like
#11

Reason: Cell F16 is formatted as text.
If a cell is formatted as text, any value entered into the cell will be entered as text without any conversion: you cannot enter a numeric value or formula.
When opening your file in Excel, the problem is the same.


Currently, your format for cell F16 is " "@. If you don’t want numbers to be displayed in this cell, and you want text to be displayed with a leading space, you can use the ;;;" "@ format (note the semicolon).

1 Like
#13

response to LeroyG
Thank you very much for providing the undesired email solution. I’m just now beginning with the AskLibreOffice web pages. I think it would have been a long time, if ever, for me to find the information!
.
response to karolus
One thing I see being disregarded is that "pre"formatted is not the same as "post"formatted. The cell was preformatted “General.” It was subsequently, after the formula had been entered post formatted to " “@. Hence, my “no” to number 3.
The other is LibreOffice 7.4 Help document does not state ‘Warning: Using " "@ as a result display code’ will result in the formula being displayed instead of the results.
.
response to sokol92
I realize ;;;” "@ is another format method. I have resorted to it, as needed, in spreadsheets I’ve created in the past. However, according to LibreOffice 7.4 Help document, " "@ should also work. Refer attached Example_2.ods
.
From LibreOffice 7.4 Help subsection:
«Text and Text, it is stated “To include text in a number format that is applied to a cell that might contain text, enclose the text by double quotation marks (” "), and then add an at sign (@). For example, enter "Total for "@ to display “Total for December”.»
.
. The above is the instruction. It does not state to work correctly, that is to display results instead of formula, “;;;” or anything else is required.

Perhaps someone knows where in LibreOffice 7.4 Help it states that using " "@ will result in the formula being displayed instead of the results. I have not found in LibreOffice 7.4 Help “If a cell is formatted as text, any value entered into the cell will be entered as text without any conversion: you cannot enter a numeric value or formula.” I can not find any intimation in LibreOffice 7.4 Help this is true.
.
If the statements in the paragraph above can not be found, then, with all due respect, those offering help, are offering opinions or workarounds: not solutions in accord with LibreOffice 7.4 Help instructions. And, if a format or command does not work in accord with instructions, then the probability is that it is a bug.
.
I would like to see evidence from LibreOffice 7.4 Help stating using the format " "@ will cause a forumula in which the results are text to display as a formula.

#14

Forgot to attach the spreadsheet “Example_2.ods.” Apologize.

It is attached.
Example_2.ods (25.8 KB)

#15

If I may…

The file Example_2.ods has inaccurate instructions. In cell C23 it says “If cell c20, above, is edited…” In order to replicate the problem that you describe, the cell to be edited has to be cell C22, not C20.

Having said that, the real problem is a misunderstanding of the “number format code”.

The “@” in the number format code is a “placer” for (literal) text content. In the context of LO Calc, a cell (by itself) can contain either:
_ a (numeric) Value of some type;
_ a Text value;
_ or a Formula.

Those are possible contents of a cell.

Now, in this context, a Formula could result in a numeric value, or a text value. There could even be cases (e.g. the IF() function) in which you could get a numeric result or a text result, depending on the outcome of the formula, for the same cell (which contains such kind of conditional formula).

In the “Example 2” file, cell B5 is an example of a Text content, whereas cells B11 and C22 are both considered Formulas in the context of LO Calc. The respective results of those formulas are both text, but they are still formulas.

In the “Example 2” file, the adequate number format code for cell C22 should be “General” (or “Standard”). This is not a workaround, but rather the real solution to the problem.

Let’s say I need a Text value that looks like a number. I will format the cell as Text, and then type-in the value. After that, I cannot directly use such cell in typical math formulas, because that cell does not contain a “number”; only a Text value that looks as a number.

The number format code is one method to modify the way the content of a cell is displayed, but not the actual value stored in the cell. If the content of a cell is “61.1” (without quotation marks), that numeric value could be displayed in several ways; it will still be (just) the same number – there are caveats to this, but let’s keep it simple.

When a cell contains “=61.1” (without quotation marks), that is already a Formula, and it can also get its own numeric format code. In Calc, menu “View > Value Highlighting” will show a numeric value in blue and a formula in green – don’t forget to reset the Value Highlighting setting again back to “off” :).

With additional experience, complex number format codes can be used, and Styles in LO are much more prominently needed (and used) than in other popular spreadsheet tools.

#16

From the Help for 7.4, linked from the the page the Help button takes you to from the Format Cells dialogue Number Format Codes

Or just looking at the category in the Format Cells dialogue where it says it is Text in cell C22

#17

Documentation:

You can format numbers as text in LibreOffice Calc. Open the context menu of a cell or range of cells and choose Format Cells - Numbers, then select “Text” from the Category list. Any numbers subsequently entered into the formatted range are interpreted as text. The display of these “numbers” is left-justified, just as with other text.

If you have already entered normal numbers in cells and have afterwards changed the format of the cells to “Text”, the numbers will remain normal numbers. They will not be converted. Only numbers entered afterwards, or numbers which are then edited, will become text numbers.

Perhaps these formulations aren’t clear enough. :slight_smile:
In the examples, you first entered a formula into a cell and then applied a format from the Text category. According to paragraph 2 of the quote, the cell’s value didn’t change.
According to paragraphs 1 and 2, after changing the cell format, any characters entered will be interpreted as text (as I mentioned above). This prevents you from entering formulas and numbers into this cell (until you remove the cell’s text format).

#18

Response to ady

Thank you for catching my typo in Example_2.ods. You are correct: cell 23 should state “If cell C22, above, is edited …”
.
I will note, and this is significant, " "@ works in accord with LibreOffice 7.4 Help formatting instructions when the formula is created and format is first used. It is when the formula in the cell is subsequently edited that the cell displays the formula instead of the results. It is editing the formula, NOT the " "@ that causes the problem. I hope all who respond will keep this in mind.

In paragraph beginning with ‘In the "Example 2: file …’
.
The <<adequate format code for cell C22 should be “General” (or “Standard”)>>. This is NOT a solution to the problem. The format “General” does not prefix with three spaces to the left as does the format " "@ for the formula that results in text. And, the format is relevant for BOTH literal text and text resulting from a formula. Nothing that I’ve found in LibreOffice 7.4 Help states or implies otherwise.

.
Continuing, the discussion beginning with “Let’s say I need a Text value that looks like a number.” strays from the context of my original post. I want my spreadsheet cell to display TEXT, NOT a number. While some parts of the discussion may be supported by instructions in LibreOffice 7.4 Help, the other parts are NOT.

A comment
In my original post, about a week ago, I queried “Is there anything that can be done to prevent or eliminate this problem?” While I accede, LibreOffice Calc is not Excel, in Excel, when the problem occurs, it can be eliminated for all problematic cells by replacing all equals “=” symbols with “=”. But as far as I know, in Excel, latest versions, the problem can not be prevented.

A question about Ask LibreOffice: Is there a way to display the dates a post was made?

#19

The order of those actions is relevant. When you first introduce a formula (or edit one) and then you set the number format code to Text, the result is not the same as first setting a number format code and then introducing a formula.

After setting the cell format to Text, any new content that you introduce (or edit) will be considered as text. The initial “=” is no longer instructing Calc to consider the rest as a formula; it is all Text in that cell. The same happens with content that would look as a number; it will be text.

Easy to fix. Use the “General” (“Standard”) number format code. Then, instead of starting the formula with:

="My brother weighs

you start the formula with the same initial double quotation mark, then the consecutive space characters, and then the rest:

="     My brother weighs

You could make it more prominent (so you don’t delete the space characters by mistake, for example):

="     "&"My brother weighs

Please be aware that the type of font will affect the way this is displayed. For instance, a fixed-width (monospace) font might show the result slightly differently than a proportional font.

#20

Instead of the date, the site shows how long ago the post was written:

Screenshot_20260430-092758
Screenshot_20260430-092758812×184 17.9 KB

#21

April 30 2026
.
Response to Wanderer
Thank you for disclosing how the time or date a post was made. The number of days or hours passed are difficult to see. I don’t know why the days or hours passed and other symbols on the web page are so light. I have to open the curtain just right and make sure I’m directly in front of the screen to discern the light text and symbols. Personally, I’d like BLACK text and symbols.
.
Response to ady
In discussion beginning with “The order of those actions is relevant…,” I have found nothing, nothing in LibreOffice 7.4 Help that states, intimates or implies that supports the statement or remaining discussion in the paragraph. If it is in the LibreOffice 7.4 Help instructional information, please make a screenshot and present it.
.
As for prefixing my desired output with three spaces, this is a workaround with which I am familiar. There’s no way I’m going to modify, in this manner, spreadsheets I created since 1984 (It was called Multiplan then. The name change to Excel came about 1985 or 1986). That would be too much work. You may note in the list below, there is " “@” " that can be used regardless whether the text is aligned in the cell as left or right. Three spaces can prefix or suffix in correlation with text alignment as desired.
.
As for formats, I understand them. I’ve created many. To demonstrate, a list copied from my basic Excel and LibreOffice Calc template spreadsheets follows:
.
mmm d yyyy
mmmm d yyyy
h:mma/p
h:mm:ssa/p
h:mm
h:mm:ss
mmm d yyyy\ h:mm
mm:ss
mm:ss.0
[h]:mm:ss
hh\°mm’ss"
hh.°mm’ss"
###\ ###\ ##0;-###\ ###\ ##0;0;@" "
###\ ###\ ##0.0;-###\ ###\ ##0.0;0.0;@" "
###\ ###\ ##0.00;-###\ ###\ ##0.00;0.00;@" "
###\ ###\ ##0.000;-###\ ###\ ##0.000;0.000;@" "
###\ ###\ ##0.0000;-###\ ###\ ##0.0000;0.0000;@" "
###\ ###\ ##0.00;[Red]-###\ ###\ ##0.00;0.00;@" "
###\ ##0.00" “;[Red]-###\ ##0.00” “;”"
###\ ##0.00" “;[Red]-###\ ##0.00” “;0.00” "
###\ ###\ ##0.00;[Red]-###\ ###\ ##0.00;"";@" "
###\ ###\ ##0.00;[Red]-###\ ###\ ##0.00;" "
###\ ###\ ##0.00" “;[Red]-###\ ###\ ##0.00” “;” "
0+00.00
00\eNJ .00’00"
#\ ?/?
#\ ??/??
@" "
" “@
" “@” "
@”: "
@" = "
@": "
" = “@
.
I create others as needed. The @” " and editing formats worked as defined in help and User Manuals until about Excel 2007(?). I don’t remember when the problem began in LibreOffice Calc.

#22

Sounds like the earlier Excel had a bug. LibreOffice 4.0.0.3 acts the same way as LibreOffice 26.2.2.2 and Excel 2010

#23

Please allow me to kindly and respectfully doubt about such claim that Excel 2007 behaves differently than Excel 2010 regarding number format codes.

We might claim that LO Calc works in a certain way – I mean, in practical terms; I am not referring to theoretic claims, standards, manuals, help content or whatever else other than practical experience – but let’s not pretend that Calc has no issues (or that is perfectly consistent) regarding number format codes; that is not true.

Additionally, please keep in mind that number format codes are also dependent on file format. A number format code might be supported in Excel for xlsx files, and Calc might not support the same behavior for ods files, or might not import/export the format correctly for xlsx files.

Unfortunately, nowadays I have no access to any Excel version of any kind, so I have no way to replicate the necessary steps in Excel (starting anew from scratch with a clean empty spreadsheet). At any rate, I don’t know whether the result of such test would change anything in Calc.

#24

I would like to suggest a possible alternative number format code that might help you solve that situation, instead of having to add the space characters to the formula itself.

Instead of using " "@, please try this alternative code:
[>0]"";[<0]"";"";" "@

(In the last section of the code, there should be several space characters in-between quotation marks before the “at” symbol “@”.)

Please at least test that number format code once on a new clean empty cell, and only then type-in the formula that you intend to use (and then save and reload the test file). If that works for you, we can look for some method to improve cells where a formula is already typed-in.

#25

Cells
 
Format

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.