How to disable formula view in the document

michaldybczak · December 6, 2020, 11:59am

It became very frequent that the documents I get from various sources have some sort of weird Formula lockdown, so it always shows a formula instead of the result, at least when I add my own columns which I want to fill with my formulas.

I already have under Settings->Calc unticked “show formula”.
Sometimes there is a space before = sign and that also blocks the formula.

However, the most of the time, there is some hidden blockage that I can’t get rid of. The only way to make it work is to copy all the data, open new document and paste “only values” or copy it to MS Word. When I use “values and formats” the blockage is also copied, so it suggests the issue is somewhere in formats, but I don’t know where.

The blockage happens in all possible office programs so this isn’t a matter of the usual office settings. It is something saved in the document itself. Even when I use the formula’s creation, it shows the expected result of the formula and it’s all correct, but when the formula is applied, I see the formula, not the expected result.

Please, help me find the solution for this problem.

LeroyG · December 6, 2020, 2:20pm

I am guessing that the cells are formatted as Text.

Add Answer is reserved for solutions. Please, click edit below your question to add more information.

michaldybczak · December 6, 2020, 2:58pm

No, they are formatted as count. I tried changing formats but that didn’t bring any solution.

LeroyG · December 6, 2020, 3:08pm

Click edit below your question and use the paper clip to upload a sample file (remove all sensible data before).

anon73440385 · December 6, 2020, 4:24pm

No, they are formatted as count. I tried changing formats but that didn’t bring any solution.

Re-formatting won’t bring back a formula being text to a formula evaluating values. It keeps being a text looking like a formula unless you edit the text/formula. So I support @LeroyG’s assumption that they are in fact text. One(!!) method could be:

Format as “General”
and use Edit -> Find and Replace to Replace ) by itself ) (which is an edit operation) - or use Data -> Text To Column, (which is also an edit operation)

michaldybczak · December 6, 2020, 7:56pm

Finally, it worked!
What I did:

Create the column, it becomes by default a text column
Change the formatting to General
Create formula.
Use Text to Column

The order of the actions matter, because when I changed formatting or used Text to Column on already existing formula, nothing was changed.

Thanks @anon73440385! I wouldn’t figure it out if you haven’t mentioned that reformatting doesn’t change anything if the formula isn’t edited (which isn’t possible, because the formula must be the same).

I feel like this is a badly designed program.
First, the default text formatting (even all other columns around have different formatting: data on the left, numbers on the right, the column was created in the middle of them) is a problem. Why General formatting isn’t the default, like on MS Word?
Second, changing formatting should update it, because it’s not intuitive or easy to delete the values, change formatting again and then enter the formula again, so the formula would work.

michaldybczak · December 6, 2020, 8:00pm

How can I mark comment or answer as SOLVED or SOLUTION? I don’t see the option for it. This forum engine is very unfriendly :(.

LeroyG · December 6, 2020, 8:21pm

Find and Replace, when not using functions, need to be done with \= in the Find: field, Regular expressions checked, and = in the Replace: field.

michaldybczak · December 6, 2020, 7:58pm

@anon73440385 delivered the explanation that lead me to the solution:

What I did:

Create a new column, it becomes by default a text column
Change the formatting to General
Create formula.
Use Text to Column

Order of the action matters, otherwise the same actions won’t make any change…

Eh… LO… you need to work on functionality a lot to be usable professionally :(.

LeroyG · December 6, 2020, 8:18pm

@michaldybczak, If you insert a cells to the right of cells with x format, the new cells will inherit the x format. This is intentional.

I can’t understand why, if you changed the format to General (for me is Standard) and then created the formula, you needed to use Text to Column. Maybe you copied the formulas from cells formated as text.