LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 02-12-2017, 04:56 PM   #1
grumpyskeptic
Member
 
Registered: Apr 2016
Posts: 341

Rep: Reputation: Disabled
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.

Last edited by grumpyskeptic; 02-12-2017 at 04:58 PM.
 
Old 02-12-2017, 05:09 PM   #2
beachboy2
Senior Member
 
Registered: Jan 2007
Location: Wild West Wales, UK
Distribution: Linux Mint 20 MATE, MX-19.3, antiX, EndeavourOS
Posts: 3,323
Blog Entries: 17

Rep: Reputation: 1262Reputation: 1262Reputation: 1262Reputation: 1262Reputation: 1262Reputation: 1262Reputation: 1262Reputation: 1262Reputation: 1262
grumpyskeptic,

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

https://help.libreoffice.org/Calc/Us...Number_Formats
 
Old 02-12-2017, 05:53 PM   #3
hydrurga
LQ Guru
 
Registered: Nov 2008
Location: Pictland
Distribution: Linux Mint 20 MATE
Posts: 8,048
Blog Entries: 5

Rep: Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917
Quote:
Originally Posted by beachboy2 View Post
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.

Last edited by hydrurga; 02-12-2017 at 05:54 PM.
 
Old 02-12-2017, 06:05 PM   #4
hydrurga
LQ Guru
 
Registered: Nov 2008
Location: Pictland
Distribution: Linux Mint 20 MATE
Posts: 8,048
Blog Entries: 5

Rep: Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917
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.
Old 02-12-2017, 06:21 PM   #5
beachboy2
Senior Member
 
Registered: Jan 2007
Location: Wild West Wales, UK
Distribution: Linux Mint 20 MATE, MX-19.3, antiX, EndeavourOS
Posts: 3,323
Blog Entries: 17

Rep: Reputation: 1262Reputation: 1262Reputation: 1262Reputation: 1262Reputation: 1262Reputation: 1262Reputation: 1262Reputation: 1262Reputation: 1262
Smile

hydrurga,

You are quite right, wrong end of stick.

It is well past my bedtime!
 
Old 02-13-2017, 05:03 PM   #6
grumpyskeptic
Member
 
Registered: Apr 2016
Posts: 341

Original Poster
Rep: Reputation: Disabled
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.
 
Old 02-13-2017, 05:09 PM   #7
hydrurga
LQ Guru
 
Registered: Nov 2008
Location: Pictland
Distribution: Linux Mint 20 MATE
Posts: 8,048
Blog Entries: 5

Rep: Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917Reputation: 2917
Here is a link to info on the logical functions:

https://help.libreoffice.org/Calc/Logical_Functions
 
Old 02-13-2017, 05:58 PM   #8
grumpyskeptic
Member
 
Registered: Apr 2016
Posts: 341

Original Poster
Rep: Reputation: Disabled
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.

Last edited by grumpyskeptic; 02-15-2017 at 01:42 AM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] slackware_14.2_current_beta_2 Libreoffice5.1_crash X Error: BadMatch Regnad Kcin Slackware 2 02-06-2016 06:15 AM
Does anyone recognise this icon? clifford227 General 7 10-27-2012 04:59 AM
do you like OO Calc? newbiesforever Linux - Software 20 10-22-2009 03:05 PM
test26.s doesn't recognise DVD drive, other kernels won't recognise HDD v@ny@ Slackware - Installation 15 05-31-2007 06:03 AM
Anyone recognise this? brundles Linux - General 5 03-24-2005 08:45 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 06:37 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration