I'm scraping from Finviz, how do I correct the 1.17B result to standard form? (Google Sheets)-CodePudding

So I have the input to scrape on Google Sheets as follows:

=VALUE(SUBSTITUTE( index(IMPORTHTML("http://finviz.com/quote.ashx?t="&$C7,"table", 9),3,2),"*",""))

The C7 box contains the ticker SBSW and the result shows 1.17B, however because there is the B, it is showing #VALUE!

I'd like to know how to convert the 1.17B to the Standard form of the number. How can I adapt my current code to accomplish that?

Edit: the same issue occurs with '-' (hyphen) results.

CodePudding user response：

Use regexextract() and regexreplace(), like this:

=value( 
  regexextract( 
    regexreplace( 
      regexreplace( 
        query( 
          importhtml("http://finviz.com/quote.ashx?t=" & C7, "table", 9), 
          "select Col2 where Col1 = 'Income' ", 0 
        ), 
        "B", "e9" 
      ), 
      "M", "e6" 
    ), 
    "[-\d.e] " 
  ) 
)

To learn the exact regular expression syntax used by Google Sheets, see RE2.