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

Question

Is there a cleaner 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.

`const mult = s => parseFloat(s) * {k:1e3, m:1e6, b:1e9}[s.at(-1).toLowerCase()];` — Jaromanda X, Aug 31 '22 at 02:56
A sheets formula wouldn't be too complex. This would handle up to quadrillion... `=value(mid(A1,1,len(A1)-1))*iferror(1000^match(right(A1,1),{"K","M","B","T","Q"},0),1)` — pgSystemTester, Aug 31 '22 at 02:57
@JaromandaX Was wondering if there was a function of sorts. Maybe "faster" wasn't the correct word, perhaps cleaner? or shorter? — dan, Aug 31 '22 at 03:02
@pgSystemTester I'm actually familiar with this, however with the way I've set things up already, I think it would be more trouble if I did it via a sheet formula. I think I would have to adjust columns as well as make sure my scripts still work after the changes, etc. — dan, Aug 31 '22 at 03:04
@Rubén My plan was to use switch cases and just multiply the number out depending on the abbreviation, so *10^3 for K, 10^6 for M, etc., but I was wondering if there was a function out there that would reduce the amount of code I need to write. — dan, Aug 31 '22 at 03:05
Possible originals: https://stackoverflow.com/q/69773823/1595451, https://stackoverflow.com/q/64863336/1595451 — Rubén, Aug 31 '22 at 03:17
Reopened. Rationale: OP is asking the reverse of what was in the linked duplicate. A=>B !== B=>A — TheMaster, Aug 31 '22 at 23:09

pgSystemTester · Accepted Answer · 2022-08-31T16:27:52.920

3

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)+0),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;
  }
}

edited Aug 31 '22 at 16:27

answered Aug 31 '22 at 03:30

pgSystemTester

8,979
2
23
49

the filter formula doesn't work for the case of 786 – David Leal Aug 31 '22 at 03:40
Oh thanks. I didn't see that requirement. Updating in 30 seconds. – pgSystemTester Aug 31 '22 at 03:44
Need to upate the JS formula too... hang on – pgSystemTester Aug 31 '22 at 03:46
still not working, the condition `isnumber(right(A1,1))` doesn't work properly for the case of 786. It should be only: `ISNUMBER(A2:A)` – David Leal Aug 31 '22 at 04:02
1

It works if you combine to a single line. For viewing purposes, I split it, and accidentally chopped the cell address. I'll update. – pgSystemTester Aug 31 '22 at 05:31

David Leal · Answer 2 · 2022-08-31T03:35:35.243

1

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)))),))

edited Aug 31 '22 at 03:35

answered Aug 31 '22 at 03:29

David Leal

6,373
4
29
56

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

2 Answers2

Linked