I have cut and pasted numbers with dollar sign [ $ ] looking like this [ $29,825 ] that are formatted as text. When I format them as “currency” a single quote character [ ’ ] appears in front of them. How to remove the single quote character?
“Find and Replace” command doesn’t find the character.
3 Likes
This is still a frustrating problem and the faq linked with the solution is either down or no longer available.
It is still available but there are infrastructure problems today.
Meanwhile you can access an archived version at https://web.archive.org/https://wiki.documentfoundation.org/Faq/Calc/How_to_convert_number_text_to_numeric_data .
In order to avoid this problem, you simply have to check “Detect special numbers” in the text import dialog (this takes care of currencies, dates, times, percents). and you have to choose the right import langauge as US-English, UK-English or whatever describes the incoming data best. If currencies look like 1.234,98€ and dates are written like 31.12.2021 “German (Germany)” would be the right import locale. With the right import locale and “special numbers” you can nail down most text import problems.
This solution doesn’t work for me at all.
I have found multiple FAQ instructions to deal with this and followed each of them carefully. In no case does it remove the apostrophe. My problem is simple: I have a column full of entries like .123 and .234. I want to format those cells as numbers with 2 decimal places as I can do in other Calc spreadsheets, but in this spreadsheet, nothing I do will remove the apostrophe, nor will it allow me to apply the number formatting. When I right-click the cell and go to Format Cells … it identifies the cell as Number, 2 Deciminal places and shows the Format Code as 0.00, but when I okay that dialog box, the number still shows as .123 and when I edit the cell it shows the weird invisible apostrophe. I’ve tried search and replacing for all the strange codes suggested in all the related threads, but none of them works. The only way I’ve found to fix it is to tediously enter EACH cell and manually delete the annoying apostrophe. I can’t begin to tell you how frustrated I am with Libre Calc. I started using it because Excel is so horrible in terms of making a spreadsheet look right. Calc fixed many of the glaring problems in Excel, but has added all sorts of buggy and counter-intuitive problems not present in Excel. Why couldn’t they just make it work like Excel and remove the bugs?
You are dramatizing the situation. It’s not really a problem.
So you are doing something wrong…
I tried every element of the UI and followed every cryptic set of instruction on this forum. None worked except (finally) the one that told me to copy the entire column to the clipboard, paste it into notepad, save, copy from notepad and paste back in. That worked. Clearly there’s a bug that renders a cell unable to respond to the command to format it as a number instead of as text. It thinks it’s a number, it says its a number, but it behaves like text.
Try this:
- in a fresh Calc doc, type .234 into a cell
- right click and format the cell as a number with two decimal places
result: it show .23
That’s how it’s supposed to work
- find a cell that - when double clicked - shows its contents as starting with an apostrophe and that otherwise contains a number such as .234
- right-click and format the cell as a number with two decimal places
result: it still shows 3 decimal places and still, when edited, shows the apostrophe that’s not visible when the cells is not in edit mode - try all the search and replace steps shown here
result: the same - enter the cell and manually delete the apostrophe
result: not this single cell behaves properly but there’s no way to remove the apostrophes from a range of cells - as stated above, copy the entire column, pasted into notepad, save, paste back.
I “dramatize” the situation as you so condescendingly suggest after having worked full time as a software tester for many years, and after have spent many hours beta-testing Calc for free, reporting multiple bugs until finally throwing my hands up in frustration at the passive aggressive arrogant idiocy of this horrible program. The cell borders graphics were the final insult. And yes, it’s still better than Excel and that’s why I’m still here but it is extremely frustrating as you can see from the volume of people complaining about this bug. Why couldn’t the mission statement have been: “Create a open code version of Excel. Fix the bugs.” Why reinvent and break all the parts of Excel that WEREN’T broken?
1 Like
I wouldn’t comment on the “style” of the recent post by @beatlefan.
However, it may look like spreadsheet software developed into a real mess over decades. The many, many, and even more wee little features (in specific automatisms), highly doubtable options, and the like lead to a situation where even experienced users sometimes can’t overlook the interferences of this and that with that and this. However, much of that “progress” was driven a great deal by users requesting doubtable features again and again - and of course “compatibility” with …
Anyway, the issue under discussion here should actually be sufficiently belabored. If there still occur problems the afflicted users should consider “strange” causes like corrupted user profiles, (??e.g.??) mixing up “typographic” single quotes with apostrophes, or even unwittingly working on cells actually formatted to lie in one or another strange way. Formats can lie a lot - just as if they were invented for the purpose.
Sorry for my bad “style” but I’ve been at this for a long time and I use this program every day.
corrupted user profiles
Interestingly, I deleted my user profile only a week ago as part of a suggestion to remedy a 100% crash I had with vertical scrolling (and or page up page down). So in my case, it’s a fresh profile. By the way, doing so didn’t fix the crash, but turning off hardware acceleration completely fixed it, so if anybody is getting scrolling crashes, that solution worked perfectly for me, as did the copy-to-notepad workaround for this current issue. The giant problem with Calc (which I’m still using instead of Excel) is not so much a bug as a design error such that the eye perceives a single border between two cells but the UI for changing the appearance of that border thinks that it’s both the left side of B2 and the right side of A2, and behaves in accordingly unpredictable and bizarre ways. It’s the epitome of WhatYouSeeIsNOTWhatYouGet. Excel is fine for the math; terrible for the appearance of the data. Calc seems to have found everything bad about Excel and fixed it, but then they found the few graphic elements that do work in Excel and broke them unnecessarily!
Hallo
@beatlefan a “number” starting with a decimal dot isnt a number, so you need to add a 0 in front:
search for
^\.\d+$
replace with
0&
[x]regular Expression
Excel works 100% same way in this regard, as Calc does.
This argument appears again and again. Every newbie comes across this (myself included). E.g., the reasons are explained in LibreOffice Calc not computing functions (showing function, not output) - #18 by mikekaganski. Then, we have tdf#150264 filed recently, with some discussion and mockup.
And I simply do not believe that the steps outlined in this exact thread (both those posted by myself, and those listed in the FAQ that @erAck created) didn’t work when followed correctly. I would be glad to help a question like “I follow these steps, it doesn’t work, let me explain what I see on each step and where it diverges” … it would be useful also to find ways to improve our description if they happen to confuse people (even though I create screencasts to reduce that possibility) - maybe there’s some set of data that resists our steps…
1 Like
Thanks! So “.16” is just text as far as Calc is concerned but “0.16” is a number?
3 questions:
-
How did I inadvertently create the phantom apostrophes in the first place? Or to ask it a different way, how could I create a cell with a phantom apostrophe so I can try your test? (I used the notepad workaround so I no longer have any cells with phantom apostrophes).
-
What does the phantom apostrophe actually mean technically speaking?
-
If I’d selected the whole column and formatted it as text would that have removed the phantom apostrophes?
No. You likely respond to this:
which is wrong. And the very presence of the apostrophe - the visual clue, that tells user “the value contained in the number-formatted cell looks like a number, and if such data was entered anew into the cell, it would be recognized as a number; but it is in fact a string, so we show it to user with this apostrophe” - proves that.
Yes. Then there would not need to be a visual clue that the data is text - the cell is textual anyway.
1 Like
The apostrophe is not part of the text contained in the cell but only an indicator for the situation that the content is treated as text despite the fact that it could be recognised as numerical.
If allowing RegEx you ‘Search For’ .* (anything) and ‘Replace With’ & (everything found) a new recognition process will be applied with the effect that the content is reciognised as a number and displayed in the chosen (or automatically assigned) format.
If the contents you want recognised are filling a range of a single column you can achieve the result also applying the tool ‘Data’ > ‘Text to Columns…’ (without additional measures).
(There are a lot of threads about the "apostrophe issue"in Calc. Just look for them.)
6 Likes
Also be careful not to use Data▸Text to Columns... if some cells in the range contain formulas (they would be deleted, only the values are retained).
Regards
1 Like
+1 to @PYS; still, in case of formula, there would not be any apostrophe in the respecting cells’ edit boxes. In my opinion, the Text to Columns is the ultimately correct tool for data conversion (while in case of Find&Replace, the conversion is kind of side effect).
Data > Text to Columns worked fine
Paste complete column in notepad or gedit and the copy back.
1 Like
In this case, you don’t need an intermediate application; simply paste back as unformatted text (Ctrl+Alt+Shift+V).
Yes Possible.