2

I've copied some data from a website into a sheet that displays dollar values in millions or billions, eg $239 M, $1.23 B, etc.

How can I convert these to numeric values?

[edit] Found an Excel example, but when I tried to convert to sheets, the result is always zero:

=IFS( RIGHT(B4,1)="M", N(SUBSTITUTE(B4," M","")), RIGHT(B4,1)="B", N(SUBSTITUTE(B4," B","")) *1000 )

maxhugen
  • 1,870
  • 4
  • 22
  • 44

3 Answers3

3

use:

=INDEX(IF(REGEXMATCH(A1:A4&"", "M"),
 REGEXEXTRACT(A1:A4, "\d+.\d+|\d+")*1000000, 
 IF(REGEXMATCH(A1:A4&"", "k"),
 REGEXEXTRACT(A1:A4, "\d+.\d+|\d+")*1000,
 IF(REGEXMATCH(A1:A4&"", "B"),
 REGEXEXTRACT(A1:A4, "\d+.\d+|\d+")*1000000000, A1:A4))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
0

For complex conversions, you can create a user defined Javascript function in Apps-Script and use it in sheets.

Advantages:

  1. It is easy to write complex functions using JavaScript.
  2. You can add tests to verify your code.
Rishabh Agarwal
  • 1,988
  • 1
  • 16
  • 33
0

Acording to @player0 better formula is

=LET(c,C6, INDEX(IF(REGEXMATCH(c&"", "M"), REGEXEXTRACT(c, "[-+]?(?:\d*\.*\d+)")*10000000, 
 IF(REGEXMATCH(c&"", "k"), REGEXEXTRACT(c, "[-+]?(?:\d*\.*\d+)")*10000,
 IF(REGEXMATCH(c&"", "B"), REGEXEXTRACT(c, "[-+]?(?:\d*\.*\d+)")*10000000000, c)))))

Also, you can use named functions in Google Sheets for this.

Eugene Chernyavsky
  • 61
  • 1
  • 1