Convert currency compact notation (e.g. '$1.5k') into integer (e.g. 1500)

Question

I have a column with different currencies and ways to write numbers that I want to convert to numbers, ex: $1.5M €1.5B $1.5B $1.5k €1.5k $1.5K

All the .tests I run on my regex return false. I think the issue is that the value returned has square brackets but I don't know how to take only the number. When I put a log, it shows: Info [1.5] and not Info 1.5

    function ConvertToInteger (Sheet,columnToConvert) {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sh = ss.getSheetByName(Sheet);  
  var shLastRow = sh.getLastRow();
  var shdata = sh.getDataRange().getValues(); 
  var regExp = new RegExp('[.,0123456789]+');
  
  var regExpBillionDollar = new RegExp('[\$][.,0-9]+[B$]','g');
  var regExpMillionDollar = new RegExp('[\$][.,0-9]+[M$]','g');
  var regExpKiloDollar = new RegExp('[\$][.,0-9]+[K$]','g');
  var regExpBillionEuros = new RegExp('[\‎€][.,0-9]+[B$]','g');
  var regExpMillionEuros = new RegExp('[\‎€][.,0-9]+[M$]','g');
  var regExpKiloEuros = new RegExp('[\‎€][.,0-9]+[K$]','g');  


  
  for (var i = 1; i < shLastRow; i++) {
    
    var DataConverted = regExp.exec(shdata[i][columnToConvert])
    if (regExpBillionDollar.test(DataConverted))    {
    sh.getRange(i+1, columnToConvert+1).setValue((DataConverted *1000) * 0.9); //Convert to Euros
    }
  }
}

user_11299053 · Answer

If you need to do both (convert to integer and get exchange rate product) within one function, you can make use of String.prototype.replace() which accepts callback function as its second argument. You may have this function to process matching groups (([\$\€]) - currency, either dollar or euro; (\d+.\d+) - floating point number, i.e. 1.5; ([K,M,B]) - respective suffix) in a way that

suffix is turned into multiplier (10 in power of 3, 6, 9, which corresponds to value of respective object key {'K': 10**3, 'M': 10**6, 'B': 10**9}
currency character is turned either into 1 (for dollar) or 1.16 (exchange rate for euro)

To cast resulting string into number, you may simply prepend +, to drop decimal part (equivalent to Math.floor()), you may use 0|:

const src = ['$1.5M','€1.5B','$1.5B','$1.5k','€1.5k','$1.5K', '$800'],
      exRateToDollar = {'$': 1, '€': 1.16}, 
      
      translate = (s, exchangeRate) => 
        0|+s
          .toUpperCase()
          .replace(
            /^([\$\€])(\d+(\.\d+)?)([K,M,B])?$/, 
            (_, currency, n, __, suffix) =>           
              exchangeRate[currency]*
              n*
              (suffix ? {'K':10**3, 'M':10**6, 'B':10**9}[suffix] : 1))
        
console.log(src.map(s => translate(s, exRateToDollar)))

.as-console-wrapper{min-height:100%;}

Convert currency compact notation (e.g. '$1.5k') into integer (e.g. 1500)

1 Answers