In google sheets - how return *1.54M* as $1,540,000 with the same formula that I would return $1.35B* as $1,350,000,000?

Question

I am building a stock model - where I use =importhtml() to give me financial data pulled from a website on certain stocks.
It is returning it correctly, but there are asterisks surrounding all the data as seen below:

Ticker       Market Cap      Income
 *AAPL*      *1151.68B*      *57.53B*
 *ABT*       *144.46B*       *3.60B*
 *AMD*       *58.17B*        *328.00M*

(no actual spaces are between asterisks - just avoiding HTML )

So I am taking that data and using =if(B3="*-*",0,mid(B3,2,LEN(B3)-2)) to get rid of the asterisks:

Ticker   Market Cap   Income
 AAPL     1151.68B    57.53B
 ABT      144.46B     3.60B
 AMD      58.17B      328.00M

That's working - but I want it to be able to recognize that there is either a "B" or an "M" at the end, take that char off and then multiply it by either a billion or a million, depending.

I can take my second asterisk-free table and then make a THIRD table using:

=if(right(B95,1) = "B",LEFT(B95,Len(B95)-1)*1000000000,if(right(B95,1) = "M", LEFT(B95,Len(B95)-1)*1000000))

That does the trick. But I would like figure out how to merge these two into one command so I don't have to make the third table.

Answer 1

Here's a way to do it with one formula :

=ArrayFormula(
if(REGEXMATCH(transpose({regexextract(A2,"[\w]*"),regexextract(A2,"[\w]* ([\w|\.]*)"),regexextract(A2,"[\w]* [\w|\.]* ([\w|\.]*)")}),"A"),
transpose({regexextract(A2,"[\w]*"),regexextract(A2,"[\w]* ([\w|\.]*)"),regexextract(A2,"[\w]* [\w|\.]* ([\w|\.]*)")})
,
if(REGEXMATCH(transpose({regexextract(A2,"[\w]*"),regexextract(A2,"[\w]* ([\w|\.]*)"),regexextract(A2,"[\w]* [\w|\.]* ([\w|\.]*)")}),"[0-9]B"),
regexextract(transpose({regexextract(A2,"[\w]*"),regexextract(A2,"[\w]* ([\w|\.]*)"),regexextract(A2,"[\w]* [\w|\.]* ([\w|\.]*)")}),"[^B]*")*power(10,9),
if(REGEXMATCH(transpose({regexextract(A2,"[\w]*"),regexextract(A2,"[\w]* ([\w|\.]*)"),regexextract(A2,"[\w]* [\w|\.]* ([\w|\.]*)")}),"[0-9]M"),
regexextract(transpose({regexextract(A2,"[\w]*"),regexextract(A2,"[\w]* ([\w|\.]*)"),regexextract(A2,"[\w]* [\w|\.]* ([\w|\.]*)")}),"[^M]*")*power(10,6)
,
)
)))

You can replace the spaces in the regexp with \* if your data is always the same.

Answer 2

Array Formula

• IMPORTHTML(...) is a placeholder for your existing formula that returns the source data.
• This aporoach multiplies numbers in the millions by 10^6, and billions by 10^9, and returns everything else unchanged.

=INDEX(LET(
  arr,SUBSTITUTE(IMPORTHTML(...),"*",),  
  pwr,(REGEXEXTRACT(arr, "^\d+\.\d{2}(M|B)")="B")*3+6, 
  IFNA(LEFT(arr, LEN(arr)-1)*10^pwr, arr))) 

1. arr is your array of data from IMPORTHTML after removing all the asterisks.

2. pwr calculates the exponent to be used depending on the number:

   1. REGEXEXTRACT extracts an "M" or "B" from the end of a number string with two decimal places.
      • The result will be either "M", "B", or if the regular expression isn't matched #N/A!
   2. Instead of testing conditions as follows:

   IF("M|B"="M", 6, IF("M|B"="B", 9,)
   

   It is simpler to start from "M" and adjust by 3 digits if "B" is matched. This is possible because any outcome other than "M|B" is an error:

   6+("M|B"="B")*3
   

   3. pwr will therefore be either 6, 9, or #N/A!

3. The remaining step strips the last character "M|B" from the value before multiplying it by 10^pwr.

   • For any value where REGEXEXTRACT failed to match the value and therefore pwr=#N/A!, this last step, too, will fail and return#N/A! which will be replaced by the original arr value using the IFNA Function.