# 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!

Tags :

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
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
November 08, 2018 22:56 PM