Calc will not perform calculations [after import]

[boxofrox]

I’ve imported an xlsx spreadsheet into LibreOffice and now formulas don’t work.

I read I have mis- set something = Calc will not perform calculations! Formulas do not work!, but nothing there fixes this issue.

What I see in my forumula cell is =concatenate($A$1," and ", A2) instead of the evaluated result.

Tried, but doesn’t work:

• press F9 with the formula cell selected,
• press Ctrl + Shift + F9 with the formula cell selected,
• change the cell format from Text to All, (although my result is the concatenation of text, so the format should be text),
• turn off View Formula in Tools > Options

If I create a new document, the formulas evaluate just fine into their results.

If I create a new document, and copy the fields from the broken spreadsheet over, the new document is infected with the same problem.

Update

Apparently, the issue isn’t the import, but rather once a cell is formatted as Text, it is very difficult to convert the cell back to something that recognizes formula.

[boxofrox]

After screwing around with this for over an hour, I’ve found a work around.

1. select the infected cells

2. change cell formatting from Text to All.

3. edit each infected cell and prepend a double quote. e.g.

    =concatenate(A1, " and ", B1)     becomes     "=concatentate(A1, " and ", B1)
   

4. edit each infected cell and remove the double quote you just added.

5. observe that the formula now evaluates and the result appears in the cell.

6. profit

I don’t understand what goal LO Calc is trying to achieve, but whatever they’ve done to “improve” formatting and formulas is not intuitive in this case, but rather a PITA.

[Lupp]

This is a “very traditional” problem I was fighting since about 1997: For one or another reason, may be during an import process, cells are “infected” (as you call it rather apt), with the fake number format “@” which isn’t really a number format but a directive to the process of entering, NOT to apply the automatic recognition of numbers and formulae but to take whatever entered “as is” - and that is always a text. “Switching this off”, that is newly applying an actual number format, DOES NOT CAUSE the recognition process as should be expected.

[Pedro1]

@boxofrox and @Lupp: i just realized that Excel does the same. This means that even if you Format the cell back to Number the spreadsheet will not re-read the contents so it doesn’t “realize” that it is a formula. However pasting a formula on that cell works. So this is not really a bug but something that could be improved… What is worrying is that importing an xlsx file should NOT cause this problem…

[Lupp]

@Pedro1: Many years ago I sometimes was forced to use Excel. Of course, I tested it against this “feature” and it was the way you tell. My suspicion (as in many other cases) was that StarOffice designers had decided “Better wrong than incompatible”. I twice filed a bug report to SUN regarding this issue. Now I rather think it’s a touch of sloppiness. Maybe I’ll raise a feature request “Apply recognition after ‘@’ switched off”. But I’m tired of chasing such issues for decades. It’s a workaround what I can do myself.

[Lupp]

Triggering a forced new recognition process, as is necessarty after changing from “@” to an actual number format for text that is a syntactical number and for text that is a syntactical formula as well to be recognised, can be done by:

‘Data’ > ‘Text to Columns…’ > NO separators, NO ‘Fixed width’ > ‘Standard’ in the column label > ‘OK’

Hopefully you will not again have to edit many many cells “just a bit”.

(In old times even a movement of the text cursor counted as an editing process. Now its worse. This was the progress in 17 years - or more.)

[boxofrox]

While a convoluted way to achieve my goal, it is much, much less convoluted than my answer. Well done, sir. Perhaps someday LO won’t require such meaures.