How to convert K, M, B formatted strings to plain numbers?-CodePudding

Is there a faster way to convert the following or is a function that covers each case required? I've found plenty of answers going one way but not the other. And they all seem to point towards requiring switch cases, etc.

Desired input/output:

786 remains 786
2423 remains 2423
1k becomes 1000
5.816k becomes 5816
10.61k becomes 10610
2.5m becomes 2500000
1.250064b becomes 1250064000
etc

Also looking to make it not case-sensitive, so 1k and 1K are both 1000. But that should be an easy enough modification.

CodePudding user response：

Updated with condition for no abbreviation.

I don't think it's too much overhead just use javascript to get there. The below code works, though not as fast as using a straight sheets formula such as:

=If(isnumber(right(A1,1)),A1,value(mid(A1,1,len(A1)-1))*iferror(1000^match(right(A1,1),{"K","M","B","T","Q"},0),1))

or as an array formula:

=filter(if(ISNUMBER(right(A1:A,1) 0),A1:A,value(mid(A1:A,1,

len(A1:A)-1))*iferror(1000^match(right(A1:A,1),{"K","M","B","T"},0),1)),A1:A<>"")

Using App scripts is slower, but this works. You can add in more conditions to clean your incoming string.

function unabbreviateNumber(someInput) {
  const someLetters = ["k", "m", "b", "t", "q"];
  var lastLetter = someInput.toString().slice(someInput.length - 1, someInput.length).toLowerCase();

  if (isNaN(lastLetter)){
      //example string cleanup
  someInput = someInput.replace(/ /g, ''); //removes all spaces
  someInput = someInput.replace(/\$/g, ''); //removes dollar sign


  const zOutput = parseFloat(someInput.slice(0, someInput.length - 1));
  const zMultiplier = someLetters.findIndex(x => x == lastLetter)   1;
  return zOutput * 1000 ** zMultiplier;

  }else{
    return someInput;
  }
}

CodePudding user response：

You can try something like this:

=ARRAYFORMULA(if(len(A2:A), IF(REGEXMATCH(lower(A2:A),"k$"),
    Value(SUBSTITUTE(lower(A2:A),"k",""))*1000, 
  IF(REGEXMATCH(lower(A2:A),"m$"), 
    Value(SUBSTITUTE(LOWER(A2:A),"m",""))*1000000,
  IF(REGEXMATCH(lower(A2:A),"b$"), 
    Value(SUBSTITUTE(lower(A2:A),"b",""))*1000000000,
  value(A2:A)))),))