LibreOffice5 Calc - how to recognise e.g. 1.5M as 1500000
 

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").

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.