I am using Linux Mint 17.3 Rosa Cinnamon.

I have been copying the results off the Google Stock Screener webpage and pasting them into the LibreOffice5 Calc spreadsheet. It does this with excellent ease, so 10/10 for Calc.

However the one thing it does not do is recognise e.g. 5.2M as 5200000.

Is there any way I can get it to do this rather than having to rewrite all the numbers by hand please?

Calc will not accept 5.2M as a number, and gives an error message when a cell with that in it is used in a formula.

It would also be nice to be able to be able to automatically load the data into a Linux spreadsheet, but do not know if this is possible.

Thanks.

grumpyskeptic,

In this example substitute "M" for "Million" and you should be okay:

https://help.libreoffice.org/Calc/Us...Number_Formats

I may be wrong, bb2, but I'm not sure that this is what grumpyskeptic is looking for. I think that they want to paste 12.3M, for example, into a cell and for it to be recognised as the number 12,300,000. What you're suggesting sort of works the other way round (in effect it controls display, not recognition): you paste 12300000 into a cell and it is displayed as 12.3M.

Just as a suggestion, I would have a column where I would paste my "1.2M" etc, and then a column next to it containing formulas along the lines of =VALUE(LEFT(A4,LEN(A4)-1))*1000000. Then I would use the calculated column as the basis of any further calculations.

This of course requires that the first column contains values strictly in the format of a decimal number followed immediately by the letter M (or any other letter but it would effectively mean "million").

1 members found this post helpful.

hydrurga,

You are quite right, wrong end of stick.

It is well past my bedtime!

Thank you for the replies.

Hydrurgas suggestion of using =VALUE(LEFT(A4,LEN(A4)-1))*1000000 to create another column should work, except that only about half the values in the column are in the M format.

How could I set up an if...then rule that would only apply the above if and only if the final character is M? And otherwise would just copy it unchanged.

I would also welcome any suggestions about downloading data from Google Stock Screener or historical shares data non-manually within Linux.

Thanks.

Here is a link to info on the logical functions:

https://help.libreoffice.org/Calc/Logical_Functions

Thanks. The full formula I have used is:

=if(right(a4,1)="M";value(left(a4,len(a4)-1))*1000000;a4)

Calc would not let me copy the formula so I had to copy it by hand.

Edit: I have since tried several ways to copy a formula from Calc into a text editor. The only way I have found that works is to click on a cell, highlight the contents of the slot which is towards the top of the screen, and then press Ctrl-C to copy it. As this wrecks the formula then it is best to do this on a copy of the spreadsheet.

I wish Calc would make it easier to copy or paste formulas, as it is often helpful to study and check a complicated formula outside the spreadsheet.