Is there a way to convert 1K to 1000 and 1M to 1,000,000 In LibreOffice Calc

by AliZ   Last Updated November 08, 2018 23:01 PM

I need to sum a large data set, but the values are entered as K or M. Is there a way to change these convert these to numerical values? I was thinking of using find and replace to change M to 000000, but that won't work for values such as 1.25M. Is there an easy solution to this? Thank you!



Answers 2


Assuming the values are in a column (column A in this example), add a second column with this formula and fill down to match the values you need to convert:

=IFNA(LEFT(A1,LEN(A1)-1)*CHOOSE(MATCH(RIGHT(A1,1),{"K","M"},0),1000,1000000),A1)

The formula looks for "K" or "M" with MATCH(RIGHT(A1,1),{"K","M"},0), which returns 1 for "K" or 2 for "M". The CHOOSE function returns 1,000 or 1,000,000, which is multiplied by LEFT(A1,LEN(A1)-1). The LEFT function removes the "K" or "M".

What happens if the value does not end with "K" or "M"? In these cases, MATCH returns #NA, and IFNA returns the value unchanged. The formula converts positive and negative values, as well a values with decimal places (for example, 1.25M converts to 1,250,000).

The same method is easily extended to convert billions (1,000,000,000):

=IFNA(LEFT(A1,LEN(A1)-1)*CHOOSE(MATCH(RIGHT(A1,1),{"K","M","B"},0),1000,1000000,1000000000),A1)
creidhne
creidhne
August 27, 2016 21:26 PM

public class samplecode {

public static void main(String[] args) {
    String str = "257K";
    try {
        Integer num = Integer.parseInt(str);
        System.out.println(str);
    } catch (Exception e) {
        char type = str.charAt(str.length() - 1);
        Integer num = Integer.parseInt(str.substring(0, str.length() - 1));
        switch (type) {
        case 'K':
            System.out.println(num * 1000);
            break;
        case 'M':
            System.out.println(num * 1000000);
            break;
        }
    }
}

}

user961938
user961938
November 08, 2018 22:56 PM

Related Questions





Spreadsheet formula to display most recent prices

Updated October 15, 2015 06:00 AM