Search code examples
if-statementformulalibreoffice-calc

How to use numbers present as text with different unit prefixes in calculations

I have data in a spreadsheet describing amount of data transferred over a mobile network: data in one column (over 300 rows) has three possible forms:

123,45KB  
123,45MB  
1,23GB

How can I transform or use this data in order to sum or do other calculations on numbers properly?

Solution

  • Assuming your data is in column A and there are always two characters as unit ("KB", "MB" or "GB") at the end, then the formula for transforming the data to numeric could be:

    =--LEFT(A2;LEN(A2)-2)*10^(IF(RIGHT(A2;2)="KB";3;IF(RIGHT(A2;2)="MB";6;IF(RIGHT(A2;2)="GB";9))))

    Result:

    enter image description here

    Put the formula in B2 and fill downwards as needed.

    I suspected the decimal delimiter in your locale is comma. If not, please state what it is.

    Also since this site is English, I have used English function names. Maybe you need to translate them into your language version.

    If the decimal delimiter in your locale is not comma, then you need substituting the comma with your decimal delimiter to get a proper numeric decimal value.

    For example if the decimal delimiter is dot, then:

    =SUBSTITUTE(LEFT(A2,LEN(A2)-2),",",".")*10^(IF(RIGHT(A2,2)="KB",3,IF(RIGHT(A2,2)="MB",6,IF(RIGHT(A2,2)="GB",9))))