Google Sheets: Convert Value in Millions/Billions to Number

user_190925 0
Viewed 444

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 )

google-sheets
2 Answers

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

user_56326290

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.
user_64486560
Related