Hello,
I am pasting from google sheets a column of $ figures and trying to do add sum and getting 0 result. I tried pasting special and changing the format after pasting and nothing works. VERY frustrating and illogical.
How to change the tooltip from black to a legible color?
See screen shot showing both issues:
Because of the leading $ (which is text on the clipboard, not a number format) the numbers copied from your browser have been pasted as text. You can see this because all the “numbers” are aligned to the left. If you click on the $45.00 you will see '$45.00
Easily fixed, just select your column of numbers, click Data > Text to Columns and click OK. Your text “numbers” should then become real numbers formatted as currency and your sum should return a total
It looks as if you have a theme installed. Removing the theme, or possibly changing to another theme, should fix the tooltip. Settings are in Tools > Options > LibreOffice > Personalisation. I don’t know how to change colour of tooltip itself. Cheers, Al
Hello. Thanks but I tried and it didn’t work. Also the text is the same pasted as after doing that text co columns function with no quote before the $. Can you pls try to test on your end if this happens?
My previous way to remove apostrophe was:
Remove Apostrophe in Calc
^.
&
Cheers, Al
Maybe format column as Currency before Text to Columns, although it worked fine for me. If you are still having problems please provide a made up sample file of the issue. Click edit just below your question to add to your question and click paperclip icon to upload a .ods file.
I fixed it to work without find and replace the $s. I fiddled with the program settings am not sure what did it maybe Formula > enable wildcards in formulas or Calculate > formula syntax calc or changing to USD currency default. At any rate am quite relieved!
Hello. Please see attached. I tried find and replace the character/s is/are not there. Untitled 1.ods
I get it now! The easier way is just to paste with the $ signs then do Ctrl + H and replace $ with nothing to remove them. Then the calc works! Very stupid logic of this thing that having $10 etc pasted will cause sum = 0. Is there a setting to stop this nonsense? Thanks
Please do not use the Answer field for comments that are not an answer to the original question, use add a comment instead, or click edit on your question to provide further details. Thanks.
The stupid logic probably is that the import uses the stupid locale setting the user provided during pasting the HTML text copied and it’s currency symbol does not match the $ symbol so the string provided results in cells’ type text content. So choose locale wisely, e.g. English-US, to stop that nonsense.