I cannot get a formula to work in LibreOffice Calc
i have shown steps on how to fix the formula manually, once you remove the ’ before = it works, sadly this bug exists since many years, 2020 and beyond and it exists in the latest release as well. Once the column/row format is changed from Text to Numbers the = will show an 'Apostrophe before '= which make the formulas end up not working and appear AS IT IS. Please fix this bug, it exists and must be fixed. i have attached screenshots.
Thank you
It is not fixed, and is not going to be fixed, because that is not a bug. If you have a text in a cell, it must never be converted to a number by a change of format. But there are tools to convert text to number - e.g., Text to Columns.
2 Likes
if you have a csv file and its already in the right format why would use the text to column option where text is already in a column, please advise and clarify. thanks
To elaborate a bit on the answer from @mikekaganski:
- The apostrophe is Calc’s way to signify that “this cell content looks like it should be numeric but it is in fact textual”. It is a feature, not a bug.
- When a cell is formatted for text content (by selecting Text in the left pane under the Numbers tab), any subsequent input will be taken verbatim, disregarding numeric interpretation and formula evaluation. From Calc’s perspective, you have already chosen textual content, so the “text signifier” is not required (rather, it would also be taken verbatim, as part of the input/content).
Altering cell format NEVER changes existing cell content. It alters the way content is presented/rendered. In some cases (like with Text format, true fraction format and some others) it also changes the way new input into the cell is interpreted.
1 Like
what is the solution please so do not need to remove the ’ from every cell? thanks
@mikekaganski has given an answer above for fixing existing data with a preceding apostrophe. When importing a CSV file, ensure you check Detect Special Numbers to correctly import the majority of data.
1 Like
If you find @mikekaganski’s suggestion for text-to-columns a little abstract or otherwise undesirable, you can also use find/replace:
- First format all the “offending” cells as number, like you already understood.
- With the range of cells selected, go to menu item Edit - Find and replace
- Expand the “Additional options” section of the Find/replace dialog
- Tick Regular expressions
- Search for .* (That is a period and an asterisk)
In regular expression lingo this means “search for any number of any characters” - Replace with & (only the ampersand character)
That means “replace with the same thing you found”. - Click the Replace all button
The replace initiates a new input interpretation. When text formatting is removed, numbers are accepted and number format is applied.
1 Like
Indeed: if you have a CSV, and import it, you see exactly the same dialog, as with Text to Columns; and that dialog allows you to define exactly the same things to make your CSV data interpreted correctly.
3 Likes